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