“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

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

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

Answer 2

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                                      

Answer 3

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;