>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)
CREATE TABLE #GetSpecialChar (vColumn VARCHAR(100))
–Insert test records
INSERT INTO #GetSpecialChar
SELECT ‘SF654454%^SDF&’
UNION ALL
SELECT ‘SDFDSF’
UNION ALL
SELECT ‘6351313’
UNION ALL
SELECT ‘$KLJ’
UNION ALL
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
DROP TABLE #GetSpecialChar
Above query will consider SPACE as non valid character. If you want to exclude space, use it as under:
SELECT * FROM #GetSpecialChar WHERE vColumn  LIKE ‘%[^a-zA-Z0-9 ]%’
Advertisements

Posted on March 7, 2011, in Sql Server 2005, Sql Server 2008, SQL Server Basics, TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

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: