在Oracle中查找不包含数字数据的行

Question

我试图在一个非常大的 Oracle 表中找到一些有问题的记录。即使该列是 varchar2 列，也应包含所有数值数据。我需要找到不包含数字数据的记录（当我尝试在此列上调用 to_number(col_name) 函数时，它会抛出错误）。

Answer 1

我想你可以使用 regexp_like 条件并使用正则表达式来查找任何非数字。我希望这会有所帮助？

SELECT * FROM table_with_column_to_search WHERE REGEXP_LIKE(varchar_col_with_non_numerics, '[^0-9]+');

Answer 2

要获取指示符：

DECODE( TRANSLATE(your_number,' 0123456789',' ')

例如

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 有正则表达式，因此您可以使用它来获取实际数字：

SQL> SELECT colA
  2  FROM t1
  3  WHERE REGEXP_LIKE(colA, '[[:digit:]]');

COL1
----------
47845
48543
12
...

如果存在像“23g”这样的非数字值，它将被忽略。

Answer 3

与 SGB 的答案相反，我更喜欢执行 REGEXP_LIKE() 定义我的数据的实际格式并否定它。这允许我定义像“$DDD,DDD,DDD.DD”这样的值。

在 OP 的简单场景中，它看起来像...

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^[0-9]+
 ...找到所有非正整数。如果您也想接受负整数，这是一个简单的更改，只需添加一个可选的前导减号...
SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+
要接受浮点...
SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+(\.[0-9]+)?
任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望查找与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。
);

 ...找到所有非正整数。如果您也想接受负整数，这是一个简单的更改，只需添加一个可选的前导减号...

要接受浮点...

任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望查找与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。
);

要接受浮点...

任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望查找与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。
);

...找到所有非正整数。如果您也想接受负整数，这是一个简单的更改，只需添加一个可选的前导减号...

要接受浮点...

任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望查找与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。

);

任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望查找与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。

);

...找到所有非正整数。如果您也想接受负整数，这是一个简单的更改，只需添加一个可选的前导减号...

要接受浮点...

任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望查找与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。

);

要接受浮点...

任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望查找与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。

);

...找到所有非正整数。如果您也想接受负整数，这是一个简单的更改，只需添加一个可选的前导减号...

要接受浮点...

任何格式都一样。基本上，您通常已经拥有验证输入数据的格式，因此当您希望查找与该格式不匹配的数据时……否定该格式比提出另一种格式更简单；如果您想要的不仅仅是正整数，那么对于 SGB 的方法来说，这会有点棘手。

Answer 4

用这个

SELECT * 
FROM TableToSearch 
WHERE NOT REGEXP_LIKE(ColumnToSearch, '^-?[0-9]+(\.[0-9]+)?
);

Answer 5

经过一些测试后，我想出了这个解决方案，如果有帮助的话请告诉我。

在查询中添加以下 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

Answer 6

从 Oracle 12.2 开始，函数 to_number 有一个选项 ON CONVERSION ERROR 子句，可以捕获异常并提供默认值。

这可以用于数值测试。当转换失败时简单设置NULL并过滤所有非NULL值。

示例

with num as (
select '123' vc_col from dual union all
select '1,23'  from dual union all
select 'RV12P2000'  from dual union all
select null  from dual)
select
  vc_col 
from num
where /* filter numbers */
vc_col is not null and
to_number(vc_col DEFAULT NULL ON CONVERSION ERROR) is not null
;

VC_COL   
---------
123
1,23

Answer 7

来自 http://www.dba-oracle.com/t_isnumeric.htm

LENGTH(TRIM(TRANSLATE(, ' +-.0123456789', ' '))) is null

如果有TRIM 之后字符串中剩下的任何内容都必须是非数字字符。

Answer 8

我发现这很有用：

 select translate('your string','_0123456789','_') from dual

如果结果为 NULL，则它是数字（忽略浮点数）。

但是，我有点困惑为什么需要下划线。如果没有它，以下内容也会返回 null：

 select translate('s123','0123456789', '') from dual

还有我最喜欢的技巧之一 - 如果字符串包含“*”或“#”之类的内容，则不完美：

 SELECT 'is a number' FROM dual WHERE UPPER('123') = LOWER('123')

Answer 9

经过一些测试后，根据前面答案中的建议，似乎有两个可用的解决方案。

方法 1 速度最快，但在匹配更复杂的模式方面功能较差。
方法2更灵活，但速度较慢。

方法 1 - 最快
我已在包含 100 万行的表上测试了此方法。
它似乎比正则表达式解决方案快 3.8 倍。
0替换解决了0映射到空格的问题，并且似乎不会减慢查询速度。

SELECT *
FROM <table>
WHERE TRANSLATE(replace(<char_column>,'0',''),'0123456789',' ') IS NOT NULL;

方法 2 - 速度较慢，但​​更灵活
我比较了将否定放在正则表达式语句内部或外部的速度。两者都比翻译解决方案同样慢。因此，在使用正则表达式时，@ciuly 的方法似乎最明智。

SELECT *
FROM <table>
WHERE NOT REGEXP_LIKE(<char_column>, '^[0-9]+
);

Answer 10

您可以使用以下一项检查：

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;

Answer 11

我用有问题的列进行托盘排序，并找到带有列的行。

SELECT 
 D.UNIT_CODE,
         D.CUATM,
         D.CAPITOL,
          D.RIND,
          D.COL1  AS COL1


FROM
  VW_DATA_ALL_GC  D
  
  WHERE
  
   (D.PERIOADA IN (:pPERIOADA))  AND   
   (D.FORM = 62) 
   AND D.COL1 IS NOT NULL
 --  AND REGEXP_LIKE (D.COL1, '\[\[:alpha:\]\]')
 
-- AND REGEXP_LIKE(D.COL1, '\[\[:digit:\]\]')
 
 --AND REGEXP_LIKE(TO_CHAR(D.COL1), '\[^0-9\]+')
 
 
   GROUP BY 
    D.UNIT_CODE,
         D.CUATM,
         D.CAPITOL,
          D.RIND ,
          D.COL1  
         
         
        ORDER BY 
        D.COL1