isNumber in sql

I tried this in 11g
TABLE T

X
123
-1.2e-3
abc


select x,
to_number(
xmlquery('number($X)'
passing x as x
returning content)) n
from t;
X N
------- ----------
123 123
-1.2e-3 -.0012
abc

it is quite a common task to extract numbers from varchar2 and to dig out poor quality data.

select x, to_number(x) from t;
ERROR:
ORA-01722: invalid number

A well-known PL/SQL approach would be to use exception. Ex:

create or replace function f(x varchar2)
return number is
begin return to_number(x);
exception when others then return null;
end;
/
select x, f(x) n from t;
X N
------- ----------
123 123
-1.2e-3 -.0012
abc

another approach in plain sql could involve CASE and REGEXP

select x,
case when
regexp_like(x,
‘^-?(&#92+&#92.?|&#92d*&#92.&#92d+)([eE][+-]&#92d+)?$’)
then to_number(x)
end n
from t;
X N
——- ———-
123 123
-1.2e-3 -.0012
abc

8 thoughts on “isNumber in sql”

  1. Laurent Schneider Post author

    to check if it is numeric

    select x,
    xmlquery('let $i:=if($X castable as xs:decimal)then "YES" else "NO" return $i'
    passing x as x
    returning content) isnumeric
    from t;
    X ISNUMERIC
    ------- ---------
    123 YES
    -1.2e-3 YES
    abc NO

  2. Delfino

    I used the following option, but not for scientific numbers and after introducing it fails.

    SQL> select col2
    2 ,decode (length (translate (trim (col2), ‘ +-.0123456789eE’, ‘ ‘) )
    3 ,null, ‘Yes’
    4 ,’No’) is_number
    5 from t;

    COL2 IS_
    —————————— —
    +123.123E+2E-3 Yes
    -abcde No
    123 Yes
    -1.2e-3 Yes
    abc No
    -123 Yes
    +123 Yes
    -123.123 Yes
    +123.123 Yes
    -123.123E-23 Yes
    +123.123E+2 Yes

    11 rows selected.

    I like your options, I just changed a little bit the regexp solution to include the (+) plus sign “[+-]”

    SQL> ed
    Wrote file afiedt.buf

    1 select col2,
    2 case when
    3 regexp_like(col2,
    4 ‘^[+-]?(\d+\.?|\d*\.\d+)([eE][+-]\d+)?$’)
    5 then to_number(col2)
    6 end n
    7* from t
    SQL> /

    COL2 N
    —————————— ———-
    +123.123E+2E-3
    -abcde
    123 123
    -1.2e-3 -.0012
    abc
    -123 -123
    +123 123
    -123.123 -123.123
    +123.123 123.123
    -123.123E-23 -1.231E-21
    +123.123E+2 12312.3

    11 rows selected.

  3. Laurent Schneider Post author

    ok, +1 is a valid number, good point

    I also forget that 1e10 is valid too, so the expression will be

    regexp_like(:x, ‘^[+-]?(\d+\.?|\d*\.\d+)([eE][+-]?\d+)?$’)

  4. Wayne Phillips

    My results on a 10G Rel2 DB is not corresponding with the above example.
    Please help me understand:
    10:29:35 server-user@db> select possible_num,
    10:29:35 2 case when regexp_like(possible_num,’^[+-]?(\d+\.?|\d*\.\d+)([eE][+-]\d+)?$’)
    10:29:35 3 then ‘YES’
    10:29:35 4 else ‘NO’
    10:29:35 5 end n
    10:29:35 6 from t;

    POSSIBLE_NUM N
    ——————– —
    +123.123E+2E-3 NO –> NOT a Valid Number ???
    -abcde NO
    123 YES
    -1.2e-3 YES
    abc NO
    -123 YES
    +123 YES
    -123.123 YES
    +123.123 YES
    -123.123E-23 YES
    +123.123E+2 YES

    11 rows selected.

    10:29:35 server-user@db> select possible_num,
    10:31:58 2 case when regexp_like(possible_num,’^[+-]?(\d+\.?|\d*\.\d+)([eE][+-]\d+)?$’)
    10:31:58 3 then to_number(possible_num)
    10:31:58 4 end n
    10:31:58 5 from t;

    POSSIBLE_NUM N
    ——————– ———-
    +123.123E+2E-3 –> NOT a Valid Number ???
    -abcde
    123 123
    -1.2e-3 -.0012
    abc
    -123 -123
    +123 123
    -123.123 -123.123
    +123.123 123.123
    -123.123E-23 -1.231E-21
    +123.123E+2 12312.3

    11 rows selected.

  6. Giorgio Arata

    My approach on a 9i Rel2 DB is not corresponding with any of these..

    FUNCTION is_numeric(number_str VARCHAR2) RETURN BOOLEAN IS
    x_is_numeric PLS_INTEGER := 0;
    BEGIN
    SELECT XMLELEMENT (“ROWSET”,
    XMLAGG (
    XMLELEMENT(“ROW”, xmlattributes(ROWNUM AS “num”),
    XMLELEMENT (“STRING_VAL”, REPLACE(number_str, ‘,’, ‘.’))
    ))
    ).transform(XMLTYPE.createXML(
    ” ||
    ” ||
    ” ||
    ” ||
    ” ||
    ‘ ‘ ||
    ‘ ‘ ||
    ‘ ‘ ||
    ” ||
    ” ||
    ” ||
    ”)).getStringVal() AS RES
    INTO x_is_numeric
    FROM dual;
    RETURN x_is_numeric > 0;
    END is_numeric;

  7. Giorgio Arata

    You can find the code snippet copied + pasted to Oracle PasteBin.

    For illustration purposes, a few test cases follow…


    -- TRUE
    EXEC IF is_numeric('0123') THEN DBMS_OUTPUT.PUT_LINE('TRUE'); END IF;
    -- TRUE
    EXEC IF is_numeric(0.123) THEN DBMS_OUTPUT.PUT_LINE('TRUE'); END IF;
    -- TRUE
    EXEC IF is_numeric('0.123') THEN DBMS_OUTPUT.PUT_LINE('TRUE'); END IF;
    -- TRUE
    EXEC IF is_numeric(123) THEN DBMS_OUTPUT.PUT_LINE('TRUE'); END IF;
    -- TRUE
    EXEC IF is_numeric('123') THEN DBMS_OUTPUT.PUT_LINE('TRUE'); END IF;
    -- FALSE
    EXEC IF NOT is_numeric('foo') THEN DBMS_OUTPUT.PUT_LINE('FALSE'); END IF;

Comments are closed.