"Safe" TO_NUMBER()

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)

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

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

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

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;

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

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

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
)

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

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.

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

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?