Unanswered: Email adddress format validation question
Trying to find the best way to vaildate email formats when importing records to result tables etc... the first seems to run faster, but it seems too easy, like something is getting missed.
Does anyone know if using:
WHERE email LIKE '%@%.%'
instead of using:
( CHARINDEX(' ',LTRIM(RTRIM([email]))) = 0
AND LEFT(LTRIM([email]),1) <> '@'
AND RIGHT(RTRIM([email]),1) <> '.'
AND CHARINDEX('.',[email],CHARINDEX('@',[email])) - CHARINDEX('@',[email]) > 1
AND LEN(LTRIM(RTRIM([email]))) - LEN(REPLACE(LTRIM(RTRIM([email])),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([email])))) >= 3
AND (CHARINDEX('.@',[email]) = 0 AND CHARINDEX('..',[email]) = 0) )
is any better, or are they both pretty much bring back the same result sets?
The latter is more thorough but restrictive. You may also get false positives (for example my email address ends .uk). You don't need to use LEFT and RIGHT - you can use LIKE (especially for the LEFT since it is then sargable) so it could be more efficient. Perhaps create more of a mask (off the top of my head)
Min 1 char then @ then min 1 char then . then min 2 chars.