Search notes:

Oracle SQL: TO_NUMBER

to_number converts an expression to a value whose datatype is number. 
TO_NUMBER(
   expr
 [ DEFAULT val ON CONVERSION ERROR ]
 [ fmt [ , 'nls-param' ] ]
)

select
   to_number('123456.78',   '999999D99', q'[ nls_numeric_characters = '.,' ]' ) n1,
   to_number('123,456.78', '999G999D99', q'[ nls_numeric_characters = '.,' ]' ) n2
from
   dual;
Github repository Oracle-patterns, path: /SQL/functions/type-conversion/to/number/nls-param.sql

DEFAULT ... ON CONVERSION ERROR

select
   to_number(  '22/3'  default '-1' on conversion error) n1,
   to_number(  '22.3'  default '-1' on conversion error) n2,
   to_number('  22.3'  default '-1' on conversion error) n3,
   to_number('  22.3x' default '-1' on conversion error) n4
from
   dual;
Github repository Oracle-patterns, path: /SQL/functions/type-conversion/to/number/default-on-conversion-error.sql
If cursor_sharing is set to force, this statement might cause a ORA-43907: This argument must be a literal or bind variable. error.

See also

ORA-01722: invalid number
Oracle SQL functions

Index