Friday, September 12, 2008

[sql] How to find invalid email address in SQL Server

Very awkward without regular expression. So not perfect. But, catches enough.
select email   
  from loginuser where  
     patindex ('%[ &'',":;!+=\/()<>]%', email) > 0  -- Invalid characters
  or patindex ('[@.-_]%', email) > 0        -- Valid but cannot be starting character
  or patindex ('%[@.-_]', email) > 0        -- Valid but cannot be ending character
  or email not like '%@%.%'                 -- Must contain at least one @ and one .
  or email like '%..%'                      -- Cannot have two periods in a row
  or email like '%@%@%'                     -- Cannot have two @ anywhere
  or email like '%.@%' or email like '%@.%' -- Cannot have @ and . next to each other
  or email like '%.cm' or email like '%.co' -- Camaroon or Colombia? Unlikely. Probably typos  
  or email like '%.or' or email like '%.ne' -- Missing last letter  

10 comments:

  1. Worked great for me!

    ReplyDelete
  2. Thanks! Worked for me too!

    ReplyDelete
  3. Perfect! Thanks for the code, works great!!

    ReplyDelete
  4. Thanks cute asian girl! Worked a treat!

    ReplyDelete
  5. I wrote a script a while ago that corrects them too.

    Basically it cross references domains that have email opens recorded against them and corrects several human errors. So homail(missing character)/htomail(swapped character)/jotmail(close proximity keyboard button) are all corrected.

    Details are too long to write here really so have a look at this link...

    http://www.geakeit.co.uk/2010/07/30/the-ultimate-database-email-address-validatingprocessingcorrecting-script/

    ReplyDelete
  6. Don't forget the length of a valid email address has to be > 7

    ReplyDelete
  7. Thanks....Its really helpful...

    ReplyDelete
  8. girl, that was great. I thank you a lot.

    ReplyDelete
  9. This script marks some valid emailaddresses as invalid.

    The first condition row is the error.
    Many special characters are allowed if quoted.
    Some of the special characters in this script are also valid without quotes - for example +.
    Space is most often ok if it is prefix or suffix, so a trim is in order before running this query.

    ReplyDelete