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! , @...
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'\'}
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
