10

This works:

select Name
from Table
WHERE Name like '%[^0-9A-Za-z]%'

But now I need to add the dash character to the criteria as well.

Phillip Senn
  • 46,771
  • 90
  • 257
  • 373

2 Answers2

15

use

...ESCAPE '\'

e.g.

WHERE Name like '%[^0-9A-Za-z\-]%' ESCAPE '\'

to have the final "-" treated as a literal.

davek
  • 22,499
  • 9
  • 75
  • 95
  • There is some interesting discussion on escaping [here](http://stackoverflow.com/questions/9589074/regex-should-hyphens-be-escaped). – Derek May 24 '13 at 19:16
8

Unless it's part of a range the hyphen is not a special character in LIKE patterns, so you can just add it to your pattern, e.g.:

select 
        [char]
from
    (
    select 'a' as 'char' union 
    select '-' union 
    select '$' union
    select '7'
    ) dt
where 
    [char] like '%[^A-Za-z0-9-]%'
Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • 2
    Wording this lightly differently, unless the hyphen is the first or last character in the set then it *defines* a range so for it to be treated literally it must be the first or last character. – Rhys Jones Nov 28 '19 at 08:20
  • Comment from Rhys Jones is critical, [^A-Za-z0-9-_] would fail – rickjr82 Apr 01 '20 at 20:29
  • Not to argue this is wrong, but strangely I just came across one use case where `not like '%[^A-Za-z0-9-]%'` did not work as intended, but `not like '%[^A-Za-z0-9\-]%' escape '\'` did. Curious about why – Xu Shaoyang Feb 07 '22 at 15:45