[ACCEPTED]-SQL: if cannot convert to_number set as null-oracle
I generally use translate for this because 4 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 3 can be turned into a procedure, but I'm 2 not sure that there would be terribly much 1 benefit performance-wise.
You can create a function that tries to 2 convert the string to a number and catches 1 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
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
0
With Oracle 12.2 this can be done a bit easier 6 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 5 specify a format mask, similar to the to_number() function.
I assume 4 this would be faster than using a PL/SQL 3 function, not sure about the performance 2 compared to a case with a regex. But it is definitely 1 a lot shorter.
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;
0
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.