Searching for special chars in SQL Server

Question

select * 
from table
where column LIKE '%[^0-9a-zA-Z]%'

How can I include that the following characters are also considered not to be "special" on the where clause?

(,./;#:[]{}=-_+)(*^!`\|)

Basically I'd like to return bunch of cyrillic/nordic chars or any other funny characters.

Also what's the difference between select * from table where column LIKE '%[^0-9a-zA-Z]%'

and

select * 
from table
where column NOT LIKE '%[0-9a-zA-Z]%'

To Re-iterate: I'd like to find something like this DÃƒÂº instead of Dafao.

Thanks

Tag the dbms product used. (Some of them have regexp like, other don't.) — jarlh, Aug 26 '16 at 14:24
Yes, unfortunately it recognizes the funny Cyrillic characters to be within the range bracket of a-z and A-Z. Is this something to do with the collation? — DiamondDog, Aug 30 '16 at 07:57

score 1 · Answer 1 · answered Aug 26 '16 at 14:39

1

You need to extend your like with OR concondition..

SELECT * 
FROM table
WHERE column LIKE '%[^0-9a-zA-Z]%'
     OR column LIKE "%#%" 
     OR column LIKE "%{%" OR (etc.)

answered Aug 26 '16 at 14:39

Unnikrishnan R

4,965
1
12
21

OR operator or AND? – DiamondDog Aug 26 '16 at 14:47
OR .. You have to get the values against any of these special characters.. – Unnikrishnan R Aug 26 '16 at 14:49
right.. I dont want to receive values towards those special chars. I want to get cyrillic values like ÃƒÂº with a different text format. – DiamondDog Aug 26 '16 at 14:55

score 0 · Answer 2 · edited May 23 '17 at 10:33

You should be able to simply include those characters in the same pattern you have already, just make sure to escape each of them with "\" where necessary, like this:

select * 
from table
where column LIKE '%[^0-9a-zA-Z\,\.\/\;\#\:\[\]\{\}\=\-\_\+\)\(\*\^\!\`\\\|]%'

EDIT:

To better accommodate Unicode (i.e. the nvarchar data type), there are several options discussed HERE, and one of the more elegant (IIF your default collation is ASCII or similar) seems to be to use a condition like this:

select *
from table
where column <> cast(column as varchar(500)) --length should match the base column length
or column LIKE N'%[^0-9a-zA-Z\,\.\/\;\#\:\[\]\{\}\=\-\_\+\)\(\*\^\!\`\\\|]%'

The comparison of the nvarchar value with the varchar equivalent is an easy way to identify all non-ascii-compatible characters (like Cyrillic), and then the LIKE filters further down to a subset of ASCII chars. Seems to work quite well for the purpose.

Thanks, but for some reason it doesn't return any Cyrillic or funny looking characters. Perhaps it is to do with the collation since it is recognizing them to be between a-z and A-Z? — DiamondDog, Aug 30 '16 at 07:56
@DiamondDog the answer is now edited to better handle non-ascii (Unicode) content — SlimsGhost, Aug 30 '16 at 16:41

score 0 · Answer 3 · answered Jun 29 '19 at 10:20

0

... LIKE '[[]%'

You can use [ ] to surround a special character (or range). You don't need to escape the closing bracket.

answered Jun 29 '19 at 10:20

Searching for special chars in SQL Server

3 Answers3