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

Advertisements

About Sal De Loera
I have been involved in Information Technology, specifically Data Warehousing and BI, for over 8 years. Over these years I have assumed the following roles: Software Engineer Intern, Programmer/Analyst, Sr. Data Warehouse Engineer, Data Architect, Director of Information Technology, and as most recently as a Data Warehouse / BI consultant. Additionally, my IT experience spans the following industries: Insurance, Restaurant, Mass Media, Education, and Local Government. I have created this blog primarily as a means to not only share information and case studies regarding Business Intelligence, Data Warehousing, and Data Modeling, but also receive input from the community on some of the topics that I'l cover. Looking forward to your feedback and to the exciting opportunities that come with new people and technology!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: