You are currently viewing How to find special characters in a column?
How to find special characters in a column?

Many a times bad data will be creeping in when the end users manually enter the data or copy paste from a different source. There are chances that some non-alphabetic characters gets into the system in name field, description field, so on..

On the application front end screen, they might look the same and we might not find the difference, however, in the backend they are stored with different ascii values and when we try to extract them using BI Report or hcm extract we will get blank/space character instead of the special character. We need to open the file in Notepad and enable the ascii to view the special characters.

If there are any integrations with third party systems for payroll/benefits and when they don’t accept the special characters, the entire row will error out. In order to avoid these kind of scenarios, we need to find out bad data and fix them.

Table of Contents

SQL Query to identify the special characters:

SELECT *
FROM per_person_names_f
WHERE asciistr(full_name) != full_name

This will return all rows where the employee’s full name has a special character.

SQL Query to get distinct special character values:

SELECT DISTINCT
TRANSLATE (
full_name,
'!""#$%&''()*+,-./:;<=>?@[\]^_`{|}~ 0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'!""#$%&''()*+,-./:;<=>?@[\]^_`{|}~')
special_character
FROM per_person_names_f

Once we identify the bad data, we can manually correct these rows or use HDL to correct the data.