Find all special characters in a column in SQL Server 2008

Question

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        amp    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

User · Answer

Select   from TableName Where ColumnName LIKE     A-Za-z0-9        This will give you all the row which contains any special character

User · Answer

select count    from dbo tablename where address line 1 LIKE            eSCAPE

User · Answer

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

User · Answer

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    -    amp        lt   gt                               ESCAPE

[sql-server] Find all special characters in a column in SQL Server 2008

Examples related to sql-server