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

How is the column defined? If its a varchar field, then its not a number (or stored as one). Oracle may be able to do the conversion for you (eg, select * from someTable where charField = 0), but it will only return rows where the conversion holds true and is possible. This is also far from ideal situation performance wise.

So, if you want to do number comparisons and treat this column as a number, perhaps it should be defined as a number?

That said, here's what you might do:

create or replace function myToNumber(i_val in varchar2) return number is
 v_num number;
begin
 begin
   select to_number(i_val) into v_num from dual;
 exception
   when invalid_number then
   return null;
 end;
 return v_num;
end;

You might also include the other parameters that the regular to_number has. Use as so:

select * from someTable where myToNumber(someCharField) > 0;

It won't return any rows that Oracle sees as an invalid number.

Cheers.

Answer 2

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.

Answer 3

CREATE OR REPLACE FUNCTION is_number(N IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN CASE regexp_like(N,'^[\+\-]?[0-9]*\.?[0-9]+$') WHEN TRUE THEN 1 ELSE 0 END;
END is_number;

Please note that it won't consider 45e4 as a number, But you can always change regex to accomplish the opposite.

Answer 4

Here's a simple method which :

• does not rely on TRIM
• does not rely on REGEXP
• allows to specify decimal and/or thousands separators ("." and "," in my example)
• works very nicely on Oracle versions as ancient as 8i (personally tested on 8.1.7.4.0; yes, you read that right)

SELECT
    TEST_TABLE.*,

    CASE WHEN
        TRANSLATE(TEST_TABLE.TEST_COLUMN, 'a.,0123456789', 'a') IS NULL
    THEN 'Y'
    ELSE 'N'
    END
    AS IS_NUMERIC

FROM
    (
    -- DUMMY TEST TABLE
        (SELECT '1' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT '1,000.00' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT 'xyz1' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT 'xyz 123' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT '.,' AS TEST_COLUMN FROM DUAL)
    ) TEST_TABLE

Result:

TEST_COLUMN IS_NUMERIC
----------- ----------
.,          Y
1           Y
1,000.00    Y
xyz 123     N
xyz1        N

5 rows selected.

Granted this might not be the most powerful method of all; for example ".," is falsely identified as a numeric. However it is quite simple and fast and it might very well do the job, depending on the actual data values that need to be processed.

For integers, we can simplify the Translate operation as follows :

TRANSLATE(TEST_TABLE.TEST_COLUMN, 'a0123456789', 'a') IS NULL

How it works

From the above, note the Translate function's syntax is TRANSLATE(string, from_string, to_string). Now the Translate function cannot accept NULL as the to_string argument. So by specifying 'a0123456789' as the from_string and 'a' as the to_string, two things happen:

• character a is left alone;
• numbers 0 to 9 are replaced with nothing since no replacement is specified for them in the to_string.

In effect the numbers are discarded. If the result of that operation is NULL it means it was purely numbers to begin with.