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,
‘^-?(\+\.?|\d*\.\d+)([eE][+-]\d+)?$’)
then to_number(x)
end n
from t;
X N
——- ———-
123 123
-1.2e-3 -.0012
abc

7 thoughts on “isNumber in sql”

  1. 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. 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. 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.

  4. 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;

  5. 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;
    

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>