Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Question 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:

    WHERE
    ( 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?

    Thanks for sharing your insight.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Looks like T-SQL from where I am standing. Moving to SQL Server. Any mod spot something I missed please relocate.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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)
    Code:
     
    LIKE '_%@_%.__%'
    Min 1 char then @ then min 1 char then . then min 2 chars.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    most people use regex for this kind of thing, although you don't have that option in t-sql.

    The regex for the RFC 822 definition of what constitutes a valid address is amusing, see link below.

    http://www.regular-expressions.info/email.html

  5. #5
    Join Date
    Nov 2005
    Posts
    2
    Great insights! I didnt think about the .uk etc..... Thanks! And I've never used regex before. Thanks so much for the link! I will read up on it! Thank you both!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •