我如何检查oracle中的表在某些列/行中是否具有垃圾值? [英] How can i check if the table in oracle has garbage value in its some column/row?

问题描述

我有一个表name_data.它通过自动化过程获取数据. 我要检查的是-该表中是否有垃圾值. 请考虑以下情形:

I have a table name_data. It gets data in it through automated process. What i want to check is- if that table has any garbage value in it. Consider the below scenario:

select name_id, name_elem3, name_elem4 from name_data where name_id='200012884';

This query gives this result:
Name_id      name_elem3      name_elem4
200012884   GENEVI�VE       MARRTIN

现在在上面的结果中.数据具有垃圾值GENEVIï½½VE" 我想要一个查询或脚本，可以检查表中的垃圾值

Now in the above result. The data has garbage value "GENEVI�VE" i want a query or a script that can check the table for garbage value

推荐答案

定义垃圾.如果要查找Oracle视为非数字的特殊字符(此处为¿"和½")，则可以使用以下示例:

Define garbage. If you are looking for special characters that Oracle regards as non-alnum ('¿' and '½' here), you can use this for instance:

select * 
from mytable
where regexp_like(name_elem3,'[^[:alnum:]]')
   or regexp_like(name_elem4,'[^[:alnum:]]');

根据需要更改图案.

另一种选择是使用TRANSLATE删除所有所需的字符并检查是否还保留其他字符:

Another option is to use TRANSLATE to remove all desired characters and check if other characters remain:

select * 
from mytable
where length(translate(name_elem3, '¿ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', '¿')) > 0
   or length(translate(name_elem4, '¿ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', '¿')) > 0;

(由于Oracle奇怪的空字符串为null"，您需要在此处使用'?'或其他带有TRANSLATE的特殊字符，其中第二个字符串文字为空，因此为null，因此为"unknown"，从而TRANSLATE也会始终导致未知" = NULL.)

(You need the '¿' or any other special character with TRANSLATE here, because of Oracle's weird "empty string is null" thing, where the second string literal would be empty, hence null, hence "unknown", such that TRANSLATE would always result in "unknown" = NULL, too.)

这篇关于我如何检查oracle中的表在某些列/行中是否具有垃圾值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！