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 ]%'