"Safe" TO_NUMBER()

OracleType ConversionOra 01722

Oracle Problem Overview


SELECT TO_NUMBER('*') FROM DUAL

This obviously gives me an exception: > ORA-01722: invalid number

Is there a way to "skip" it and get 0 or NULL instead?

The whole issue: I have NVARCHAR2 field, which contains numbers and not almost ;-) (like *) and I need to select the biggest number from the column.

Yes, I know it is a terrible design, but this is what I need now... :-S

UPD:

For myself I've solved this issue with

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)

Oracle Solutions


Solution 1 - Oracle

From Oracle Database 12c Release 2 you could use TO_NUMBER with DEFAULT ... ON CONVERSION ERROR:

SELECT TO_NUMBER('*' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

Or CAST:

SELECT CAST('*' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

db<>fiddle demo

Solution 2 - Oracle

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+(\.\d+)?')), 0) 

will also get numbers with scale > 0 (digits to the right of the decimal point).

Solution 3 - Oracle

I couldn't find anything better than this:

function safe_to_number(p varchar2) return number is
    v number;
  begin
    v := to_number(p);
    return v;
  exception when others then return 0;
end;

Solution 4 - Oracle

select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, '^(-|+)?\d+(\.|,)?(\d+)?$')), 0) from dual;

It will convert 123 to 123, but 123a or 12a3 to 0.

Solution 5 - Oracle

Fitting the original question and rather old skool

select a, decode(trim(translate(b,'0123456789.',' ')),null,to_number(b),0)  from 
(
    select '1' a, 'not a number' b from dual
    union
    select '2' a, '1234' b from dual
)

Solution 6 - Oracle

It's probably a bit messy rolling your own regexp to test for a number, but the code below might work. I think the other solution by Gabe involving a user defined function is more robust since you are using the built in Oracle functionality (and my regexp is probably not 100% correct) but it might be worth a go:

with my_sample_data as (
  select '12345' as mynum from dual union all
  select '54-3' as mynum from dual union all
  select '123.4567' as mynum from dual union all
  select '.34567' as mynum from dual union all
  select '-0.3462' as mynum from dual union all
  select '0.34.62' as mynum from dual union all
  select '1243.64' as mynum from dual 
)
select 
  mynum, 
  case when regexp_like(mynum, '^-?\d+(\.\d+)?$') 
    then to_number(mynum) end as is_num
from my_sample_data

This will then give the following output:

MYNUM	IS_NUM
-------- ----------
12345	12345
54-3	
123.4567	123.4567
.34567	
-0.3462	-0.3462
0.34.62	
1243.64	1243.64

Solution 7 - Oracle

select DECODE(trim(TRANSLATE(replace(replace(A, ' '), ',', '.'), '0123456789.-', ' ')),
              null,
              DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '.', INSTR(replace(replace(A, ' '), ',', '.'), '.') + 1),
                     0,
                     DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '-', 2),
                            0,
                            TO_NUMBER(replace(replace(A, ' '), ',', '.'))))) A
  from (select '-1.1' A from DUAL union all select '-1-1' A from DUAL union all select ',1' A from DUAL union all select '1..1' A from DUAL) A;

This code excludes such strings as: -1-1, 1..1, 12-2 and so on. And I haven't used regular expressions here.

Solution 8 - Oracle

A combination of previous solutions (from @sOliver and @Mike Meyers) and trying to grab as much numbers as possible by removing the last '$' from REGEXP.

It can be used to filter the actual number from a configuration table, and have a "kind-of" comment next to the number as '12 Days'.

with my_sample_data as ( select '12345' as mynum from dual union all select '123.4567' as mynum from dual union all select '-0.3462' as mynum from dual union all select '.34567' as mynum from dual union all select '-.1234' as mynum from dual union all select '**' as mynum from dual union all select '0.34.62' as mynum from dual union all select '24Days' as mynum from dual union all select '42ab' as mynum from dual union all select '54-3' as mynum from dual ) SELECT mynum, COALESCE( TO_NUMBER( REGEXP_SUBSTR( mynum, '^(-|+)?\d*(.|,)?(\d+)?') ) , 0) is_num FROM my_sample_data;

would give


MYNUM    IS_NUM






12345    12345

123.4567 123.4567

-0.3462  -0.3462

.34567   0.34567

-.1234   -0.1234

**       0

0.34.62  0.34

24Days   24

42ab     42

54-3     54

12345 12345
123.4567 123.4567
-0.3462 -0.3462
.34567 0.34567
-.1234 -0.1234
** 0
0.34.62 0.34
24Days 24
42ab 42
54-3 54

Solution 9 - Oracle

Best method seems to be the function solution but if you don't have necessary privileges in the environment you are struggling (like me), then you can try this one:

SELECT
 CASE
  WHEN
     INSTR(TRANSLATE('123O0',
                     ' qwertyuıopğüasdfghjklşizxcvbnmöçQWERTYUIOPĞÜASDFGHJKLŞİZXCVBNMÖÇ~*\/(){}&%^#$<>;@€|:_=',
                     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
					 ),
       'X') > 0
  THEN 'Y'
  ELSE 'N'
END is_nonnumeric
FROM DUAL

By the way: In my case the problem was due to "," and "." :) So take that into consider. Inspired from this one. Also this one seems more concise.

By the way 2: Dear Oracle, can you please create some built-in functions for such small but invaluable needs?

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionzerkmsView Question on Stackoverflow
Solution 1 - OracleLukasz SzozdaView Answer on Stackoverflow
Solution 2 - OraclepweitzmanView Answer on Stackoverflow
Solution 3 - OracleGabeView Answer on Stackoverflow
Solution 4 - OraclesOliverView Answer on Stackoverflow
Solution 5 - OraclestjohnroeView Answer on Stackoverflow
Solution 6 - OracleMike MeyersView Answer on Stackoverflow
Solution 7 - OracleusboView Answer on Stackoverflow
Solution 8 - OracleVictor HView Answer on Stackoverflow
Solution 9 - OracleGultekinView Answer on Stackoverflow