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 []
.