SQL 在 Oracle 中查找不包含数字数据的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7957423/
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
Finding rows that don't contain numeric data in Oracle
提问by Ben
I am trying to locate some problematic records in a very large Oracle table. The column should contain all numeric data even though it is a varchar2 column. I need to find the records which don't contain numeric data (The to_number(col_name) function throws an error when I try to call it on this column).
我试图在一个非常大的 Oracle 表中找到一些有问题的记录。该列应包含所有数字数据，即使它是 varchar2 列。我需要找到不包含数字数据的记录（当我尝试在此列上调用 to_number(col_name) 函数时会引发错误）。
回答by SGB
I was thinking you could use a regexp_like condition and use the regular expression to find any non-numerics. I hope this might help?!
我在想你可以使用 regexp_like 条件并使用正则表达式来查找任何非数字。我希望这可能会有所帮助？！
SELECT * FROM table_with_column_to_search WHERE REGEXP_LIKE(varchar_col_with_non_numerics, '[^0-9]+');
回答by Michael Durrant
To get an indicator:
获取指标：
DECODE( TRANSLATE(your_number,' 0123456789',' ')
e.g.
例如
SQL> select DECODE( TRANSLATE('12345zzz_not_numberee',' 0123456789',' '), NULL, 'number','contains char')
2 from dual
3 /
"contains char"
and
和
SQL> select DECODE( TRANSLATE('12345',' 0123456789',' '), NULL, 'number','contains char')
2 from dual
3 /
"number"
and
和
SQL> select DECODE( TRANSLATE('123405',' 0123456789',' '), NULL, 'number','contains char')
2 from dual
3 /
"number"
Oracle 11g has regular expressions so you could use this to get the actual number:
Oracle 11g 具有正则表达式，因此您可以使用它来获取实际数字：
SQL> SELECT colA
2 FROM t1
3 WHERE REGEXP_LIKE(colA, '[[:digit:]]');
COL1
----------
47845
48543
12
...
If there is a non-numeric value like '23g' it will just be ignored.
如果有像“23g”这样的非数字值，它将被忽略。
回答by ciuly
In contrast to SGB's answer, I prefer doing the regexp defining the actual format of my data and negating that. This allows me to define values like $DDD,DDD,DDD.DD In the OPs simple scenario, it would look like
与 SGB 的回答相反，我更喜欢使用正则表达式来定义我的数据的实际格式并否定它。这允许我定义像 $DDD,DDD,DDD.DD 这样的值在 OPs 简单场景中，它看起来像
SELECT *
FROM table_with_column_to_search
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^[0-9]+$');
which finds all non-positive integers. If you wau accept negatiuve integers also, it's an easy change, just add an optional leading minus.
它找到所有非正整数。如果你也接受负整数，这是一个简单的改变，只需添加一个可选的前导减号。
SELECT *
FROM table_with_column_to_search
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+$');
accepting floating points...
接受浮点数...
SELECT *
FROM table_with_column_to_search
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+(\.[0-9]+)?$');
Same goes further with any format. Basically, you will generally already have the formats to validate input data, so when you will desire to find data that does not match that format ... it's simpler to negate that format than come up with another one; which in case of SGB's approach would be a bit tricky to do if you want more than just positive integers.
任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望找到与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。
回答by Anil
Use this
用这个
SELECT *
FROM TableToSearch
WHERE NOT REGEXP_LIKE(ColumnToSearch, '^-?[0-9]+(\.[0-9]+)?$');
回答by JAY SOPARIYA
After doing some testing, i came up with this solution, let me know in case it helps.
在做了一些测试之后，我想出了这个解决方案，如果有帮助，请告诉我。
Add this below 2 conditions in your query and it will find the records which don't contain numeric data
在您的查询中添加以下 2 个条件，它将找到不包含数字数据的记录
and REGEXP_LIKE(<column_name>, '\D') -- this selects non numeric data
and not REGEXP_LIKE(column_name,'^[-]{1}\d{1}') -- this filters out negative(-) values
回答by capitano666
From http://www.dba-oracle.com/t_isnumeric.htm
来自http://www.dba-oracle.com/t_isnumeric.htm
LENGTH(TRIM(TRANSLATE(, ' +-.0123456789', ' '))) is null
If there is anything left in the string after the TRIM it must be non-numeric characters.
如果在 TRIM 之后字符串中还剩下任何内容，则它必须是非数字字符。
回答by Wouter
After doing some testing, building upon the suggestions in the previous answers, there seem to be two usable solutions.
在进行了一些测试之后，基于之前答案中的建议，似乎有两个可用的解决方案。
Method 1 is fastest, but less powerful in terms of matching more complex patterns.
Method 2 is more flexible, but slower.
方法 1 速度最快，但在匹配更复杂的模式方面功能较弱。
方法 2 更灵活，但速度较慢。
Method 1 - fastest
I've tested this method on a table with 1 million rows.
It seems to be 3.8 times faster than the regex solutions.
The 0-replacement solves the issue that 0 is mapped to a space, and does not seem to slow down the query.
方法 1 - 最快
我已经在一个有 100 万行的表上测试了这个方法。
它似乎比正则表达式解决方案快 3.8 倍。
0-replacement 解决了 0 映射到一个空间的问题，并且似乎不会减慢查询速度。
SELECT *
FROM <table>
WHERE TRANSLATE(replace(<char_column>,'0',''),'0123456789',' ') IS NOT NULL;
Method 2 - slower, but more flexible
I've compared the speed of putting the negation inside or outside the regex statement. Both are equally slower than the translate-solution. As a result, @ciuly's approach seems most sensible when using regex.
方法 2 - 较慢，但更灵活
我比较了将否定放在正则表达式语句内部或外部的速度。两者都比翻译解决方案慢。因此，@ciuly 的方法在使用正则表达式时似乎是最明智的。
SELECT *
FROM <table>
WHERE NOT REGEXP_LIKE(<char_column>, '^[0-9]+$');
回答by egor7
You can use this one check:
您可以使用此检查：
create or replace function to_n(c varchar2) return number is
begin return to_number(c);
exception when others then return -123456;
end;
select id, n from t where to_n(n) = -123456;
回答by aiGuru
I've found this useful:
我发现这很有用：
select translate('your string','_0123456789','_') from dual
If the result is NULL, it's numeric (ignoring floating point numbers.)
如果结果为 NULL，则为数字（忽略浮点数。）
However, I'm a bit baffled why the underscore is needed. Without it the following also returns null:
但是，我有点困惑为什么需要下划线。没有它，以下也返回空值：
select translate('s123','0123456789', '') from dual
There is also one of my favorite tricks - not perfect if the string contains stuff like "*" or "#":
还有一个我最喜欢的技巧——如果字符串包含“*”或“#”之类的东西，那就不完美了：
SELECT 'is a number' FROM dual WHERE UPPER('123') = LOWER('123')
