Check for special characters in column of a SQL Table

When we work with raw data, there may be chances of raw data in any column. As a DBA you had to check for any special / junk characters in you your table. You just had to check whether your column contains any non-alphanumeric characters. Just return that rows. If you want to allow any more characters to your column, you can alter your query accordingly.

Here is the script to find  non-alphanumeric characters in the column:

SELECT *
FROM YourTableName
WHERE ColumnName like '%[^a-Z0-9 ]%'

From this query, you will get that particular rows of non-alphanumeric characters. For removing those special characters from the column, replace special character with nothing ''.

UPDATE TableName
SET ColumnName =
(
CASE
WHEN [Name] LIKE '%[^a-zA-Z0-9 ]%'
THEN Replace(REPLACE( Name, SUBSTRING( Name, PATINDEX('%[~,@,#,$,%,&,*,^,&,%,!,'',",*,(,)]%', Name), 1 ),'') ,'-',' ')
--THEN REPLACE( Name, SUBSTRING( Name, PATINDEX('%[~,@,#,$,%,&,*,^,&,%,!,'',",*,(,)]%', Name), 1 ),'')
ELSE [Name]
END
)

Happy Coding !!!