Search code examples
oracle-databasetype-conversionora-01722

"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)
Solution

  • 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;