T-SQL Tip: Sniffing out special characters in a table using LIKE

Here’s a handy snippet of code if you need to find special characters in a table. I had to implement this for a client after discovering that a third party export process was not correctly handling special characters (i.e. the per table export would flip question marks to upside down question marks…weird huh!). We needed to find a way to identify those tables that contained special characters and develop custom exports for these tables only. To address the aforementioned issue, the developer can use the SQL Server system tables to retrieve meta-data of their tables and auto-generate code similar to the snippet below for all applicable tables and columns (i.e. nchar, char, varchar, etc).

CREATE TABLE #SpecialCharTable

(
colA VARCHAR(50),
colB VARCHAR(50),
colC VARCHAR(50)
)

INSERT INTO #SpecialCharTable
VALUES      (‘A’,  ‘B’, ‘C’)

INSERT INTO #SpecialCharTable
VALUES      (‘A’, ‘B’, Char(63))

INSERT INTO #SpecialCharTable
VALUES      (Char(1), Char(2), Char(3))

SELECT colA,
colB,
colC
FROM   #SpecialCharTable
WHERE  ( colA LIKE ‘%[^0-9a-zA-Z ]%’ )
OR ( colB LIKE ‘%[^0-9a-zA-Z ]%’ )
OR ( colC LIKE ‘%[^0-9a-zA-Z ]%’ )

SELECT TOP 1 *
FROM   #SpecialCharTable
WHERE  ( colA LIKE ‘%[^0-9a-zA-Z ]%’ )
OR ( colB LIKE ‘%[^0-9a-zA-Z ]%’ )
OR ( colC LIKE ‘%[^0-9a-zA-Z ]%’ )

SELECT *
FROM   #SpecialCharTable
DROP TABLE #A_SpecialCharTable

Note: PATINDEX is a handly function to find the location of special characters in a column.
http://msdn.microsoft.com/en-us/library/ms188395.aspx

%d bloggers like this: