如何确定字符串是否为 SQL 中的数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5666986/
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
How can I determine if a string is numeric in SQL?
提问by AndyGeek
In a SQL query on Oracle 10g, I need to determine whether a string is numeric or not. How can I do this?
在 Oracle 10g 上的 SQL 查询中，我需要确定字符串是否为数字。我怎样才能做到这一点？
回答by Chandu
You can use REGEXP_LIKE:
您可以使用 REGEXP_LIKE：
SELECT 1 FROM DUAL
WHERE REGEXP_LIKE('23.9', '^\d+(\.\d+)?$', '')
回答by Adriano Carneiro
回答by Evan Haas
I don't have access to a 10G instance for testing, but this works in 9i:
我无权访问 10G 实例进行测试，但这适用于 9i：
CREATE OR REPLACE FUNCTION is_numeric (p_val VARCHAR2)
RETURN NUMBER
IS
v_val NUMBER;
BEGIN
BEGIN
IF p_val IS NULL OR TRIM (p_val) = ''
THEN
RETURN 0;
END IF;
SELECT TO_NUMBER (p_val)
INTO v_val
FROM DUAL;
RETURN 1;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
END;
SELECT is_numeric ('333.5') is_numeric
FROM DUAL;
I have assumed you want nulls/empties treated as FALSE.
我假设您希望将空值/空值视为 FALSE。
回答by SQB
As pointed out by Tom Kyte in http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7466996200346537833, if you're using the built-in TO_NUMBERin a user defined function, you may need a bit of extra trickery to make it work.
正如指出的汤姆凯特http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7466996200346537833，如果您使用的是内置TO_NUMBER的用户定义的函数，您可能需要一些额外的技巧才能使其工作。
FUNCTION is_number(x IN VARCHAR2)
RETURN NUMBER
IS
PROCEDURE check_number (y IN NUMBER)
IS
BEGIN
NULL;
END;
BEGIN
PRAGMA INLINE(check_number, 'No');
check_number(TO_NUMBER(x);
RETURN 1;
EXCEPTION
WHEN INVALID_NUMBER
THEN RETURN 0;
END is_number;
The problem is that the optimizing compiler mayrecognize that the result of the TO_NUMBERis not used anywhere and optimize it away.
问题是优化编译器可能会识别出TO_NUMBER没有在任何地方使用的结果并将其优化掉。
Says Tom (his example was about dates rather then numbers):
汤姆说（他的例子是关于日期而不是数字）：
the disabling of function inlining will make it do the call to check_date HAS to be made as a function call - making it so that the DATE has to be pushed onto the call stack. There is no chance for the optimizing compiler to remove the call to to_date in this case. If the call to to_date needed for the call to check_date fails for any reason, we know that the string input was not convertible by that date format.
禁用函数内联将使其调用 check_date 必须作为函数调用进行 - 使其必须将 DATE 推送到调用堆栈上。在这种情况下，优化编译器没有机会删除对 to_date 的调用。如果调用 check_date 所需的 to_date 调用因任何原因失败，我们知道字符串输入不能按该日期格式转换。
回答by John Ryan
Here is a method to determine numeric that can be part of a simple query, without creating a function. Accounts for embedded spaces, +- not the first character, or a second decimal point.
这是一种确定数字的方法，该方法可以作为简单查询的一部分，而无需创建函数。考虑嵌入的空格、+- 不是第一个字符或第二个小数点。
var v_test varchar2(20);
EXEC :v_test := ' -24.9 ';
select
(case when trim(:v_test) is null then 'N' ELSE -- only banks, or null
(case when instr(trim(:v_test),'+',2,1) > 0 then 'N' ELSE -- + sign not first char
(case when instr(trim(:v_test),'-',2,1) > 0 then 'N' ELSE -- - sign not first char
(case when instr(trim(:v_test),' ',1,1) > 0 then 'N' ELSE -- internal spaces
(case when instr(trim(:v_test),'.',1,2) > 0 then 'N' ELSE -- second decimal point
(case when LENGTH(TRIM(TRANSLATE(:v_test, ' +-.0123456789',' '))) is not null then 'N' ELSE -- only valid numeric charcters.
'Y'
END)END)END)END)END)END) as is_numeric
from dual;
回答by Peter DeGregorio
I found that the solution
我发现解决方案
LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' '))) is null
allows embedded blanks ... it accepts "123 45 6789" which for my purpose is not a number.
允许嵌入空白......它接受“123 45 6789”，这对我来说不是一个数字。
Another level of trim/translate corrects this. The following will detect a string field containing consecutive digits with leading or trailing blanks such that to_number(trim(string1)) will not fail
另一个级别的修剪/翻译纠正了这一点。以下将检测包含连续数字的字符串字段，前导或尾随空白，以便 to_number(trim(string1)) 不会失败
LENGTH(TRIM(TRANSLATE(translate(trim(string1),' ','X'), '0123456789', ' '))) is null
回答by zlsmith86
For integers you can use the below. The first translate changes spaces to be a character and the second changes numbers to be spaces. The Trim will then return null if only numbers exist.
对于整数，您可以使用以下内容。第一个转换将空格更改为字符，第二个将数字更改为空格。如果只存在数字，则 Trim 将返回 null。
TRIM(TRANSLATE(TRANSLATE(TRIM('1 2 3d 4'), ' ','@'),'0123456789',' ')) is null
