“Safe” TO_NUMBER()

Question

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)

Answer 1

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

Answer 2

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

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

Answer 3

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.

Answer 4

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
)

Answer 5

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

Answer 6

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?