TSQL Email Validation (without regex)

by

Very basic would be:

SELECT
  EmailAddress, 
  CASE WHEN EmailAddress LIKE '%_@_%_.__%' 
            AND EmailAddress NOT LIKE '%[any obviously invalid characters]%' 
  THEN 'Could be' 
  ELSE 'Nope' 
  END Validates
FROM 
  Table

This matches everything with an @ in the middle, preceded by at least one character, followed by at least two, a dot and at least two for the TLD.

You can write more LIKE patterns that do more specific things, but you will never be able to match everything that could be an e-mail address while not letting slip through things that are not. Even with regular expressions you have a hard time doing it right. Additionally, even matching according to the very letters of the RFC matches address constructs that will not be accepted/used by most emailing systems.

Doing this on the database level is maybe the wrong approach anyway, so a basic sanity check as indicated above may be the best you can get performance-wise, and doing it in an application will provide you with far greater flexibility.

Leave a Comment

tech