If anyone is interested, I wrote a pretty useful email validation case statement. I know that I probably missed some domains, and I didn't touch two character country domains, but it's a good start, I think. I would appreciate feedback, since I'm not great at (especially) regex.

Thanks

(edit: it seems that apostrophes are allowed before the @)

with emailcheck as (

select @email as [Email],

case

when @email not like '[A-Za-z0-9_-.'']%@[A-Za-z0-9_-]%.[A-Za-z]%[A-Za-z]' then 'Invalid Format'

when len(@email) < 5 or @email is null then 'Invalid Length'

when charindex('@', @email) is null then 'Invalid Format - No @'

when charindex('.', @email) is null then 'Invalid Format - No .'

when charindex('_', @email) > charindex('@', @email) then 'Invalid _ After @'

when patindex ('%[ &,":;!+=\/()<>?]%',@email) > 0 then 'Invalid Chars'

when @email like '%.@%' or @email like '%@.%' then 'Invalid @ Seq'

when @email like '%@%@%' then 'Multiple @'

when @email like '%..%' then 'Invalid ..'

when len(substring(@email, len(@email) - charindex('.', reverse(@email)) + 2, len(@email))) = 3

and substring(@email, len(@email) - charindex('.', reverse(@email)) + 2, len(@email))

not in ('biz','cat','com','int','net','org','pro','tel','xxx','edu','gov','mil') then 'Invalid Domain 3 Chars'

when len(substring(@email, len(@email) - charindex('.', reverse(@email)) + 2, len(@email))) > 3

and substring(@email, len(@email) - charindex('.', reverse(@email)) + 2, len(@email))

not in ('aero','asia','coop','info','jobs','mobi','museum','name','travel') then 'Invalid Domain Over 3 Chars'

else 'Valid' end as [Dispo]

from @table

)

select email, dispo

from emailcheck

order by dispo