Monday, March 7, 2011

SQL Server: How to Get Strings Which Contains Special Characters

Recently I was asked for a T-SQL by using which, one can get string data, which contains special characters. During application development, data insertion containing special characters can be blocked, but there are situation when existing client data is need to be merged into your newly build system and existing data may contain strings with special characters like !,@,#,$,%,^,&,*,+ etc, which can crash your application.

In following query such records which contain special characters are searched using simple LIKE clause with list and range wild-card characters.

(Note: use ^ for NOT IN given characters LIST)

CREATETABLE #GetSpecialChar (vColumn VARCHAR(100))

--Insert test records

INSERTINTO #GetSpecialChar

SELECT'SF654454%^SDF&'

UNIONALL

SELECT'SDFDSF'

UNIONALL

SELECT'6351313'

UNIONALL

SELECT'$KLJ'

UNIONALL

SELECT'$%^'

--Use LIKE with list and range to get column values containing characters other then a to z and 0 to 1

SELECT*FROM #GetSpecialChar WHERE vColumn LIKE'%[^a-zA-Z0-9]%'

--DROP temporary table when not required

DROPTABLE #GetSpecialChar

Above query will consider SPACE as non valid character. If you want to exclude space, use it as under:

Translate

About Me

Aasim Abdullah is working as SQL Server DBA with CureMD (www.curemd.com) based in NY, USA. He has been working with SQL Server since 2007 (Version 2005) and has used it in many projects as a developer, administrator, database designer. Aasim's primary interest is SQL Server performance tuning. If he finds the time, he like to sketch faces with graphite penciles.