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?
This question is related to
sql-server
Select * from TableName Where ColumnName LIKE '%[^A-Za-z0-9, ]%'
This will give you all the row which contains any special character.
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 count(*) from dbo.tablename where address_line_1 LIKE '%[\'']%' {eSCAPE'\'}
Source: Stackoverflow.com