I have a table with a VARCHAR column which happen to contain numbers.

But I need to read them as numbers:

SELECT TO_NUMBER(my_column) FROM my_table

Easy huh?

Not so fast. Well sometimes the stored numbers are not valid numbers (like ‘123-456’) and Oracle chokes in these rows (ORA-01722). I don’t care about those rows (or later maybe I do, but in another query).

For this query, I need to select the rows whose TO_NUMBER will not fail.

SELECT TO_NUMBER(my_column) FROM my_table WHERE my_column IS CONVERTIBLE TO_NUMBER -- BEWARE THIS IS NOT VALID SQL

How?

I really don’t care if another function (besides TO_NUMBER) is used. Could be CAST. Could be anything (apart from user defined function, because I have no permission to create functions in this instance).

Any conversion will do, as long as:

1. it doesn’t choke if the string at hand is not convertible (could return null for instance)
2. it accepts US numbers even in a BR installation (yes, different decimal points); to be clear my strings are like “9.00”, this should become the (real) number 9.
3. I couldn’t care less about the invalid strings, as long as they don’t appear disguised as another valid real number (like 0).