SQL：如果不能转换为 _number 设置为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6470819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: if cannot convert to_number set as null
提问by macwadu
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.
如果我不能转换为数字集 NULL，我想做一个选择。
回答by cwallenpoole
I generally use translate for this because it is such an odd corner case:
我通常为此使用 translate ，因为它是如此奇怪的极端情况：
SELECT
CASE
WHEN 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.
如果有必要，这可以变成一个程序，但我不确定在性能方面会有很大的好处。
回答by Justin Cave
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
回答by DCookie
You could also use REGEXP_LIKE:
你也可以使用 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
回答by a_horse_with_no_name
With Oracle 12.2this can be done a bit easier using the on conversion erroroption:
在 Oracle 12. 2 中，使用以下on conversion error选项可以更轻松地完成此操作：
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.
您还可以选择指定格式掩码，类似于该to_number()功能。
I assume this would be faster than using a PL/SQL function, not sure about the performance compared to a casewith a regex. But it is definitely a lot shorter.
我认为这比使用 PL/SQL 函数更快，与使用case正则表达式相比，不确定性能。但它肯定要短得多。
回答by fdaines
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;
