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

oracle plsql oracle10g
Fgblanch picture Fgblanch · Feb 22, 2011 · Viewed 188.2k times · Source

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

Saish picture Saish · Mar 19, 2012

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.

Related questions

How to execute an oracle stored procedure?

I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT.PUT_LINE('Test'); end it …

Read More
How to use BOOLEAN type in SELECT statement

I have a PL/SQL function with BOOLEAN in parameter: function get_something(name in varchar2, ignore_notfound in boolean); This function is a part of 3rd party tool, I cannot change this. I would like to use this function …

Read More
Is it possible to output a SELECT statement from a PL/SQL block?

How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT? For example how to do a SELECT like: SELECT foo, bar FROM foobar; …

Read More