SQL: if cannot convert to_number set as null

user_613500 0
Viewed 27721

I have a table with this values:

ID      VALUE
-----------------------
23559   200
23562   -1 & {14376}#-1

and I want to do to a select that if I cannot convert to number set NULL.

oracle sql
5 Answers

With Oracle 12.2 this can be done a bit easier using the on conversion error option:

select id, cast(value as number default null on conversion error) as value
from the_table;

Optionally you can also specify a format mask, similar to the to_number() function.

I assume this would be faster than using a PL/SQL function, not sure about the performance compared to a case with a regex. But it is definitely a lot shorter.

user_3303150
Related