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

You can use the regular expression function 'regexp_like' in ORACLE (10g)as below:

select case
       when regexp_like(myTable.id, '[[:digit:]]') then
        case
       when myTable.id > 0 then
        'Is a number greater than 0'
       else
        'Is a number less than or equal to 0'
     end else 'it is not a number' end as valuetype
from table myTable

Answer 2

You can use this example

SELECT NVL((SELECT 1 FROM  DUAL WHERE   REGEXP_LIKE (:VALOR,'^[[:digit:]]+$')),0) FROM DUAL;

Answer 3

Assuming that the ID column in myTable is not declared as a NUMBER (which seems like an odd choice and likely to be problematic), you can write a function that tries to convert the (presumably VARCHAR2) ID to a number, catches the exception, and returns a 'Y' or an 'N'. Something like

CREATE OR REPLACE FUNCTION is_number( p_str IN VARCHAR2 )
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN 'Y';
EXCEPTION
  WHEN value_error THEN
    RETURN 'N';
END is_number;

You can then embed that call in a query, i.e.

SELECT (CASE WHEN is_number( myTable.id ) = 'Y' AND myTable.id > 0 
               THEN 'Number > 0'
             ELSE 'Something else'
         END) some_alias
  FROM myTable

Note that although PL/SQL has a boolean data type, SQL does not. So while you can declare a function that returns a boolean, you cannot use such a function in a SQL query.

Answer 4

Saish's answer using REGEXP_LIKE is the right idea but does not support floating numbers. This one will ...

Return values that are numeric

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

Return values not numeric

SELECT  foo 
FROM    bar
WHERE   NOT REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

You can test your regular expressions themselves till your heart is content at http://regexpal.com/ (but make sure you select the checkbox match at line breaks for this one).

Answer 5

Note that regexp or function approaches are several times slower than plain sql condition.

So some heuristic workarounds with limited applicability make sence for huge scans.

There is a solution for cases when you know for sure that non-numeric values would contain some alphabetic letters:

select case when upper(dummy)=lower(dummy) then '~numeric' else '~alpabetic' end from dual

And if you know some letter would be always present in non-numeric cases:

select case when instr(dummy, 'X')>0 then '~alpabetic' else '~numeric' end from dual

When numeric cases would always contain zero:

select case when instr(dummy, '0')=0 then '~alpabetic' else '~numeric' end from dual

Answer 6

well, you could create the is_number function to call so your code works.

create or replace function is_number(param varchar2) return boolean
 as
   ret number;
 begin
    ret := to_number(param);
    return true;
 exception
    when others then return false;
 end;

EDIT: Please defer to Justin's answer. Forgot that little detail for a pure SQL call....