PL/SQL to find Special Characters in multiple columns and tables

Question

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.

Answer 1

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.