Oracle：返回仅包含数值的行的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3932230/
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
Oracle: SQL query that returns rows with only numeric values
提问by Imageree
I have a field (column in Oracle) called X that has values like "a1b2c3", "abc", "1ab", "123", "156"
我有一个名为 X 的字段（Oracle 中的列），其值类似于“a1b2c3”、“abc”、“1ab”、“123”、“156”
how do I write an sql query that returns me only the X that hold pure numerical values = no letters? from the example above would be ?123“ and ?156“
我如何编写一个只返回包含纯数值的 X = 无字母的 sql 查询？从上面的例子中可以得到?123“和?156”
select X from myTable where ...??
select X from myTable where ...??
回答by codaddict
You can use the REGEXP_LIKEfunction as:
您可以将该REGEXP_LIKE函数用作：
SELECT X
FROM myTable
WHERE REGEXP_LIKE(X, '^[[:digit:]]+$');
Sample run:
示例运行：
SQL> SELECT X FROM SO;
X
--------------------
12c
123
abc
a12
SQL> SELECT X FROM SO WHERE REGEXP_LIKE(X, '^[[:digit:]]+$');
X
--------------------
123
SQL>
回答by Tony Andrews
If the only characters to consider are lettersthen you can do:
如果唯一要考虑的字符是字母，那么您可以执行以下操作：
select X from myTable where upper(X) = lower(X)
But of course that won't filter out other characters, just letters.
但是当然这不会过滤掉其他字符，只会过滤掉字母。
回答by andr
If you use Oracle 10 or higher you can use regexp functions as codaddict suggested. In earlier versions translatefunction will help you:
如果您使用 Oracle 10 或更高版本，您可以使用 codacci 建议的 regexp 函数。在早期版本中，translate函数将帮助您：
select * from tablename where translate(x, '.1234567890', '.') is null;
More info about Oracle translate function can be found hereor in official documentation "SQL Reference"
有关 Oracle 翻译功能的更多信息可以在这里或在官方文档“SQL 参考”中找到
UPD:If you have signs or spaces in your numbers you can add "+-" characters to the second parameter of translatefunction.
UPD：如果您的数字中有符号或空格，您可以+-在translate函数的第二个参数中添加“ ”字符。
回答by Jon Heller
What about 1.1E10, +1, -0, etc? Parsing all possible numbers is trickier than many people think. If you want to include as many numbers are possible you should use the to_number function in a PL/SQL function. From http://www.oracle-developer.net/content/utilities/is_number.sql:
1.1E10、+1、-0 等呢？解析所有可能的数字比许多人想象的要复杂。如果您想包含尽可能多的数字，您应该在 PL/SQL 函数中使用 to_number 函数。从http://www.oracle-developer.net/content/utilities/is_number.sql：
CREATE OR REPLACE FUNCTION is_number (str_in IN VARCHAR2) RETURN NUMBER IS
n NUMBER;
BEGIN
n := TO_NUMBER(str_in);
RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN 0;
END;
/
回答by Siggi
The complete list of the regexp_like and other regexp functions in Oracle 11.1:
Oracle 11.1 中 regexp_like 和其他 regexp 函数的完整列表：
http://66.221.222.85/reference/regexp.html
http://66.221.222.85/reference/regexp.html
In your example:
在你的例子中：
SELECT X
FROM test
WHERE REGEXP_LIKE(X, '^[[:digit:]]$');
回答by Sachin Shanbhag
You can use following command -
您可以使用以下命令 -
LENGTH(TRIM(TRANSLATE(string1, '+-.0123456789', '')))
This will return NULL if your string1is Numeric
如果您string1是数字，这将返回 NULL
your query would be -
您的查询将是 -
select * from tablename
where LENGTH(TRIM(TRANSLATE(X, '+-.0123456789', ''))) is null
