SQL: if cannot convert to_number set as null

Question

I have a table with this values:

ID      VALUE
-----------------------
23559   200
23562   -1 & {14376}#-1

and I want to do to a select that if I cannot convert to number set NULL.

Possible duplicate of ["Safe" TO\_NUMBER()](https://stackoverflow.com/questions/4486949/safe-to-number) — J. Chomel, Sep 16 '19 at 09:14

cwallenpoole · Accepted Answer · 2020-09-17T04:45:03.767

17

I generally use translate for this because it is such an odd corner case:

SELECT 
  CASE
     WHEN NOT TRIM(TRANSLATE(COLUMN_NAME, '1234567890', ' ')) IS NULL THEN NULL
     ELSE COLUMN_NAME
  END AS "NUMERIC_COLUMN"
FROM
  TABLE_NAME;

If necessary, that can be turned into a procedure, but I'm not sure that there would be terribly much benefit performance-wise.

edited Sep 17 '20 at 04:45

answered Jun 24 '11 at 16:36

cwallenpoole

79,954
26
128
166

+1, I like the non-procedural solution, but shouldn't TRIM(TRANSLATE(COLUMN_NAME, '1234567890', ' ')) IS NULL be TRIM(TRANSLATE(COLUMN_NAME, '1234567890', ' ')) IS NOT NULL? – DCookie Jun 24 '11 at 17:46
3

A pure translate may have issues with non-integer data (i.e. presumably, 0.99 is a valid number but 0.99.123.4 is not) and with strings that may include grouping separators (i.e. 1,000.00 is probably a valid number). – Justin Cave Jun 24 '11 at 18:38
Tanks to all for helping in a sort time, the @cwallenpoole worked for me. – macwadu Jun 24 '11 at 20:12
@JustinCave The other option is: `WHEN TRIM(TRANSLATE(COLUMN_NAME, '1234567890', ' ')) = '.' THEN NULL WHEN TRIM(TRANSLATE(COLUMN_NAME, '1234567890', ' ')) IS NULL THEN NULL` – cwallenpoole Oct 03 '14 at 15:56
@cwallenpoole - That works for a number like '0.99'. It doesn't work for, say, '1,000.99' since you'd be left with ',.'. – Justin Cave Oct 03 '14 at 16:32
@JustinCave At that point you need to start building a parsing function. – cwallenpoole Oct 03 '14 at 22:15

score 11 · Answer 2 · answered Jun 24 '11 at 16:32

11

You can create a function that tries to convert the string to a number and catches the exception. Something like

CREATE OR REPLACE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  BEGIN
    l_num := to_number( p_str );
  EXCEPTION
    WHEN others THEN
      l_num := null;
  END;

  RETURN l_num;
END;

Then you can

SELECT id, my_to_number( value )
  FROM your_table

answered Jun 24 '11 at 16:32

Justin Cave

227,342
24
367
384

1

+1 probable best solution for numbers other than unsigned integers. – DCookie Jun 24 '11 at 18:43

DCookie · Answer 3 · 2011-06-24T18:30:50.190

You could also use REGEXP_LIKE:

SELECT id
     , CASE WHEN regexp_like(value,'^[0-9]+$') THEN TO_NUMBER(value)
            ELSE NULL
        END value
  FROM your_table;

For example:

SQL> WITH q AS (
  2  SELECT 1 ID, '200' col FROM dual
  3  UNION
  4  SELECT 2, '-1 & {14376}#-1' FROM dual
  5  )
  6  SELECT id, CASE WHEN regexp_like(col,'^[0-9]+$') THEN TO_NUMBER(col) ELSE NULL END TEST FROM q;

        ID       TEST
---------- ----------
         1        200
         2

score 7 · Answer 4 · answered Oct 27 '17 at 10:21

With Oracle 12.2 this can be done a bit easier using the on conversion error option:

select id, cast(value as number default null on conversion error) as value
from the_table;

Optionally you can also specify a format mask, similar to the to_number() function.

I assume this would be faster than using a PL/SQL function, not sure about the performance compared to a case with a regex. But it is definitely a lot shorter.

score 0 · Answer 5 · answered Jun 24 '11 at 16:38

0

CREATE OR REPLACE FUNCTION asnumber(p_val IN VARCHAR2) RETURN NUMBER IS
l_val NUMBER;
BEGIN
   l_val := TO_NUMBER(p_val);
   RETURN l_val;
EXCEPTION WHEN VALUE_ERROR THEN
   RETURN null;
END;

answered Jun 24 '11 at 16:38

fdaines

1,216
10
12

SQL: if cannot convert to_number set as null

5 Answers5