SQL: how do I find if the contents of a varchar column are numeric ?

Question

One of the columns in my table is a varchar that is supposed to contain only numeric values (I can't change the definition to a number column). Thus my SQL query:

select to_number(col1) from tbl1 where ...

fails because for some row the contents of the column are not numeric.

What's a select query I can use to find these rows ?

I'm using an Oracle database

I'm looking for something like a is_number function.

Answer 1

There is no native "isnumeric" function in oracle, but this link shows you how to make one: http://www.oracle.com/technology/oramag/oracle/04-jul/o44asktom.html

Answer 2

Read this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1794145000346577404

Answer 3

You may need to decide for yourself what is numeric. Oracle will happily convert character strings in scientific notation (eg '1e10') to numbers, but it will baulk at something like '1,000' (because of the comma).

Answer 4

I don't disagree that the best solution is to follow Tom Kyte's examples others have linked to already. However, if you just need something that is SQL only because you unfortunately do not have the relationship with your DBA to add pl/sql functions to your schema you could possibly leverage regular expressions to meet a basic need. Example:

select '234', REGEXP_SUBSTR('234','^\d*\.{0,1}\d+$') from dual
union all
select 'abc', REGEXP_SUBSTR('abc','^\d*\.{0,1}\d+$') from dual
union all
select '234234abc', REGEXP_SUBSTR('234234abc','^\d*\.{0,1}\d+$') from dual
union all
select '100.4',  REGEXP_SUBSTR('100.4','^\d*\.{0,1}\d+$') from dual
union all
select '-100.4',  REGEXP_SUBSTR('-100.4','^\d*\.{0,1}\d+$') from dual
union all
select '',  REGEXP_SUBSTR('','^\d*\.{0,1}\d+$') from dual

Below is the output of the above:

INPUT      RESULT
234        234
abc        -
234234abc  -
100.4      100.4
-100.4     -