在多个列和表中查找特殊字符

I am trying to come up with a script that we can use to locate any special characters that may exist in a column of data except for period, dash or underscore, and using variables.

我正在试图找到一个脚本，我们可以用来定位在数据列中可能存在的任何特殊字符，除了句号、破折号或下划线，以及使用变量。

My Data - Employees table:

我的数据-员工表:

---------------------------------------------------------
 ID |   LASTFIRST | LAST_NAME | FIRST_NAME | MIDDLE_NAME
---------------------------------------------------------
 57 | Miller, Bob |    Miller |   &^$#*)er |      NULL
 58 |  Smith, Tom |     Smith |        Tom |         B
 59 |  Perry, Pat |     Perry |         P. |    Andrew

My Script:

我的脚本:

VAR spchars VARCHAR  
spchars := '!#$%&()*+/:;<=>?@[\\\]^`{}|~'

select *
  from (select dcid, LastFirst, Last_Name, First_Name, middle_name,    
          CASE WHEN REGEXP_LIKE(First_Name, '[ || spchars || ]*$' )
               THEN '0' ELSE '1' END AS FNSPC 
          from employees)
 where FNSPC = '0';
 /

And all rows are returned.

返回所有行。

Any idea what I am doing wrong here? I want to only select Bob Miller's row.

知道我做错了什么吗?我只想选鲍勃·米勒那一排。

1 个解决方案

#1

REGEXP, Schmegexp! ;-)

REGEXP,Schmegexp !:-)

select * from employees
where translate (first_name, 'x!#$%&()*+/:;<=>?@[\]^`{}|~', 'x') != first_name;

That translates all the special characters to nothing, i.e. removes them from the string - hence changing the string value.

这将所有特殊字符转换为零，即从字符串中删除它们——从而更改字符串值。

The 'x' is just a trick because translate doesn't work as you'd like if the 3rd parameter is null.

“x”只是个小技巧，因为如果第三个参数为null，转换就不能正常工作。

#1