SQL Server - Searching for ' characters via a [^charlist]

Question

I am trying to find all email addresses in a column that contain characters that shouldn't be in an email address (e.g. , ). The below script is working, however the ' character should also be filtered out, but I can't seem to get it into the script and have the rest of it work.

SELECT top 20 Email FROM users WHERE email LIKE '%[^-0-9a-zA-Z@.!#$%&*+-/=?^_`}{|~]%'

I tried adding it in via '' and \'' but when I do, I start getting results that contain characters that I had previously filtered out (e.g. emails with a + or . in, but no undesired character).

Any advice would be greatly appreciated, thank you.

Answer 1

I'm ignoring the underlying "How do I validate an email address?" question because that's a religious issue.

This works for me:

SELECT *
FROM (VALUES ('''john@example.com'''), ('frank^@example.com'), ('ali%ce@example.com'),('bo\b@example.com')) x (email)
WHERE email LIKE '%[^-0-9a-zA-Z@.!#$%&*+-/=?^_`}{|~'']%' 

No escaping appears to be necessary here. If you want to, you could use the optional ESCAPE clause for the % and _ characters which are reserved outside a character list. The ^ is also reserved as the first character inside a character list as the NOT indicator, so you might want to escape that one, too:

SELECT *
FROM (VALUES ('''john@example.com'''), ('frank^@example.com'), ('ali%ce@example.com'),('bo\b@example.com')) x (email)
WHERE email LIKE '%[^-0-9a-zA-Z@.!#$\%&*+-/=?\^\_`}{|~'']%' ESCAPE '\'

You might find that more explicit about what you're trying to do, so you might consider it to be more maintainable. However, that escaping isn't strictly necessary.