Tuesday, August 16, 2016

Oracle 9i - Identify records that have special characters in a database column

From Oracle 10G onwards, there are REGEXP utilities available to identify special characters in a string.
In this short blog, I would like to provide a simple SQL that can be used in Oracle 9i to identify the records that have special characters in a database column.

SELECT emp_no, emp_name, emp_name_trans
  FROM (SELECT emp_no,
               emp_name,
               TRANSLATE (
                  emp_name,
                  '00123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ ',
                  '0')
                  emp_name_trans
          FROM employee)
 WHERE emp_name_trans IS NOT NULL

Note:- This query is to find out the employees whose name has any special characters in it.