check if "it's a number" function in Oracle

Question

I'm trying to check if a value from a column in an oracle (10g) query is a number in order to compare it. Something like:

select case when ( is_number(myTable.id) and (myTable.id >0) )              then 'Is a number greater than 0'              else 'it is not a number'         end as valuetype     from table myTable 

Any ideas on how to check that?

Answer 1

One additional idea, mentioned here is to use a regular expression to check:

SELECT  foo  FROM    bar WHERE   REGEXP_LIKE (foo,'^[[:digit:]]+$'); 

The nice part is you do not need a separate PL/SQL function. The potentially problematic part is that a regular expression may not be the most efficient method for a large number of rows.