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

1 comment:

  1. Its not filter 'ø' and 'ü' as special character

    ReplyDelete

All suggestions are welcome