Find all special characters in a column in SQL Server 2008

I need to find the occurrence of all special characters in a column in SQL Server 2008. So, I don't care about A, B, C ... 8, 9, 0, but I do care about !, @, &,, etc.

The easiest way to do so, in my mind, would exclude A, B, C, ... 8, 9, 0, but if I wrote a statement to exclude those, I would miss entries that had ! and A. So, it seems to me that I would have to get a list of every non-alphabet / non-number character, then run a SELECT with a LIKE and Wildcard qualifiers.

Here is what I would run:

SELECT Col1 FROM TABLE WHERE Col1 LIKE ('!', '@', '#', '$', '%'....) 

However, I don't think you can run multiple qualifiers, can you? Is there a way I could accomplish this?

4 Answers

Negatives are your friend here:

SELECT Col1 FROM TABLE WHERE Col1 like '%[^a-Z0-9]%' 

Which says that you want any rows where Col1 consists of any number of characters, then one character not in the set a-Z0-9, and then any number of characters.

If you have a case sensitive collation, it's important that you use a range that includes both upper and lower case A, a, Z and z, which is what I've given (originally I had it the wrong way around. a comes before A. Z comes after z)

Or, to put it another way, you could have written your original WHERE as:

Col1 LIKE '%[!@#$%]%' 

But, as you observed, you'd need to know all of the characters to include in the [].

The following transact SQL script works for all languages (international). The solution is not to check for alphanumeric but to check for not containing special characters.

DECLARE @teststring nvarchar(max) SET @teststring = 'Test''Me' SELECT 'IS ALPHANUMERIC: ' + @teststring WHERE @teststring NOT LIKE '%[-!#%&+,./:;<=>@`{|}~"()*\\\_\^\?\[\]\'']%' {ESCAPE '\'} 

Select * from TableName Where ColumnName LIKE '%[^A-Za-z0-9, ]%' 

This will give you all the row which contains any special character.

select count(*) from dbo.tablename where address_line_1 LIKE '%[\'']%' {eSCAPE'\'}