Sql script to find invalid email addresses

Question

A data import was done from an access database and there was no validation on the email address field   Does anyone have an sql script that can return a list of invalid email addresses  missing    etc

User · Answer

MySQL  SELECT   FROM  emails  WHERE  email  NOT REGEXP   -a-z0-9      amp                   -a-z0-9      amp                   a-z0-9   -a-z0-9        -a-z0-9         aero arpa biz com coop edu gov info int mil museum name net org pro travel mobi  a-z  a-z     0-9  1 3     0-9  1 3     0-9  1 3     0-9  1 3      0-9  1 5

User · Answer

SELECT EmailAddress AS ValidEmail FROM Contacts WHERE EmailAddress LIKE                      AND PATINDEX     a-z 0-9        -     EmailAddress    0 GO   Please check this link  https   blog sqlauthority com 2017 11 12 validate-email-address-sql-server-interview-question-week-147

User · Answer

I find this approach more intuitive   CREATE FUNCTION  dbo   ContainsVailidEmail    Input varchar 250   RETURNS bit AS BEGIN   RETURN CASE     WHEN  Input LIKE              THEN 1     ELSE 0   END END   I call it using the following   SELECT  dbo   ContainsVailidEmail   Email  FROM  dbo   User    OR  If you are only going to use this once then why not it as a Computed Column  with the following specification    case when  Email  like              then  1  else  0  end    Then you can just use it without needing to call a function

User · Answer

I find this simple T-SQL query useful for returning valid e-mail addresses  SELECT email FROM People WHERE email LIKE                   AND PATINDEX     a-z 0-9           REPLACE email   -    a      0   The PATINDEX bit eliminates all e-mail addresses containing characters that are not in the allowed a-z  0-9                 amp   -  set of characters   It can be reversed to do what you want like this   SELECT email FROM People WHERE NOT  email LIKE                   AND PATINDEX     a-z 0-9           REPLACE email   -    a      0

User · Answer

select   from users  WHERE NOT       CHARINDEX     LTRIM RTRIM  Email       0  AND  LEFT LTRIM  Email   1   lt  gt       AND  RIGHT RTRIM  Email   1   lt  gt       AND  CHARINDEX      Email  CHARINDEX      Email    - CHARINDEX      Email    gt  1  AND  LEN LTRIM RTRIM  Email     - LEN REPLACE LTRIM RTRIM  Email               1  AND  CHARINDEX     REVERSE LTRIM RTRIM  Email       gt   3  AND   CHARINDEX       Email     0 AND CHARINDEX       Email     0

User · Answer

sel  unismankur yahoo  co in  as Email   case      when Email not like     xx        AND  Email like             AND  CHAR LENGTH       oTranslate        trim  Email            - 0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ                      0      then  N  else  Y   end as Invalid Email Ind    This works very well for me

User · Answer

I know the post is old but after a 3 months time and with various email combinations I came across  able to make this sql for validating Email IDs   CREATE FUNCTION  dbo   isValidEmailFormat         EmailAddress varchar 500    RETURNS bit AS BEGIN     DECLARE  Result bit      SET  EmailAddress   LTRIM RTRIM  EmailAddress        SELECT  Result       CASE WHEN     CHARINDEX     LTRIM RTRIM  EmailAddress      0     AND LEFT LTRIM  EmailAddress  1   lt  gt          AND RIGHT RTRIM  EmailAddress  1   lt  gt          AND LEFT LTRIM  EmailAddress  1   lt  gt   -      AND CHARINDEX      EmailAddress CHARINDEX      EmailAddress   - CHARINDEX      EmailAddress   gt  2         AND LEN LTRIM RTRIM  EmailAddress    - LEN REPLACE LTRIM RTRIM  EmailAddress              1     AND CHARINDEX     REVERSE LTRIM RTRIM  EmailAddress      gt   3     AND  CHARINDEX       EmailAddress    0 AND CHARINDEX       EmailAddress    0      AND  CHARINDEX  -    EmailAddress    0 AND CHARINDEX       EmailAddress    0      AND  CHARINDEX       EmailAddress    0 AND CHARINDEX       EmailAddress    0      AND ISNUMERIC SUBSTRING  EmailAddress  1  1     0     AND CHARINDEX       EmailAddress    0     AND CHARINDEX       EmailAddress    0     AND CHARINDEX  -     EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX   amp     EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX        EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX   lt     EmailAddress  0     AND CHARINDEX   gt     EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0     AND CHARINDEX       EmailAddress  0      AND CHARINDEX      SUBSTRING  EmailAddress  CHARINDEX       EmailAddress  1  2   0     AND CHARINDEX      SUBSTRING  EmailAddress  CHARINDEX       EmailAddress -1  2   0     AND LEN SUBSTRING  EmailAddress  0  CHARINDEX       EmailAddress    gt 1     AND CHARINDEX      REVERSE  EmailAddress    gt  2     AND CHARINDEX      REVERSE  EmailAddress    lt  5       THEN 1 ELSE  0 END       RETURN  Result END   Any suggestions are welcomed

User · Answer

go  create proc GetEmail   name varchar 22    gmail varchar 22   as  begin  declare  a varchar 22   set select  a substring  gmail charindex      gmail  len  gmail -charindex      gmail  1   if   a    gmail com   insert into table name values  name  gmail   else  print  please enter valid email address   end

User · Answer

select     email  from loginuser where patindex       amp               lt  gt       email   gt  0  -- Invalid characters or patindex      -      email   gt  0   -- Valid but cannot be starting character or patindex       -     email   gt  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        -- Cant have   and   next to each other or email like    cm  or email like    co  -- Unlikely  Probably typos  or email like    or  or email like    ne  -- Missing last letter   This worked for me  Had to apply rtrim and ltrim to avoid false positives    Source  http   sevenwires blogspot com 2008 09 sql-how-to-find-invalid-email-in-sql html  Postgres version   select user guid  user guid email address  creation date  email verified  active from user data where length substring  email address from      amp               lt  gt        gt  0  -- Invalid characters or length substring  email address from     -        gt  0   -- Valid but cannot be starting character or length substring  email address from      -       gt  0   -- Valid but cannot be ending character or email address not like           -- Must contain at least one   and one   or email address like               -- Cannot have two periods in a row or email address like               -- Cannot have two   anywhere or email address like        or email address like        -- Cant have   and   next to each other or email address like    cm  or email address like    co  -- Unlikely  Probably typos  or email address like    or  or email address like    ne  -- Missing last letter

User · Answer

DELETE  FROM  contatti   WHERE  EMail  NOT LIKE    it     AND  EMail  NOT LIKE    com     AND  EMail  NOT LIKE    fr      AND  EMail  NOT LIKE    net      AND  EMail  NOT LIKE    ru      AND  EMail  NOT LIKE    eu      AND  EMail  NOT LIKE    org      AND  EMail  NOT LIKE    edu      AND  EMail  NOT LIKE    uk      AND  EMail  NOT LIKE    de      AND  EMail  NOT LIKE    biz      AND  EMail  NOT LIKE    ch      AND  EMail  NOT LIKE    bg      AND  EMail  NOT LIKE    info      AND  EMail  NOT LIKE    br      AND  EMail  NOT LIKE    pt      AND  EMail  NOT LIKE    za      AND  EMail  NOT LIKE    vn      AND  EMail  NOT LIKE    es      AND  EMail  NOT LIKE    in      AND  EMail  NOT LIKE    dk      AND  EMail  NOT LIKE    ni      AND  EMail  NOT LIKE    ar    and put all extension you want

User · Answer

SELECT   FROM people WHERE email NOT LIKE                Anything more complex will likely return false negatives and run slower   Validating e-mail addresses in code is virtually impossible   EDIT  Related questions   I ve answered a similar question some time ago  TSQL Email Validation  without regex  T-SQL  checking for email format Regexp recognition of email address hard  many other Stack Overflow questions

User · Answer

On sql server 2016 and up  CREATE FUNCTION  DBO   F IsEmail      EmailAddr varchar 360  -- Email address to check     RETURNS BIT -- 1 if  EmailAddr is a valid email address  AS BEGIN DECLARE  AlphabetPlus VARCHAR 255           Max INT -- Length of the address          Pos INT -- Position in  EmailAddr          OK BIT  -- Is  EmailAddr OK -- Check basic conditions IF  EmailAddr IS NULL     OR  EmailAddr NOT LIKE   0-9a-zA-Z                OR  EmailAddr LIKE             OR  EmailAddr LIKE            OR  EmailAddr LIKE           OR  EmailAddr LIKE           OR  EmailAddr LIKE      -       OR  EmailAddr LIKE     -        OR  EmailAddr LIKE    -       OR CHARINDEX     LTRIM RTRIM  EmailAddr     gt  0        RETURN 0     declare  AfterLastDot varchar 360   declare  AfterArobase varchar 360   declare  BeforeArobase varchar 360   declare  HasDomainTooLong bit 0   --Control des longueurs et autres incoherence set  AfterLastDot REVERSE SUBSTRING REVERSE  EmailAddr  0 CHARINDEX     REVERSE  EmailAddr      if  len  AfterLastDot  not between 2 and 17 RETURN 0    set  AfterArobase REVERSE SUBSTRING REVERSE  EmailAddr  0 CHARINDEX     REVERSE  EmailAddr      if len  AfterArobase  not between 2 and 255 RETURN 0    select top 1  BeforeArobase value from  string split  EmailAddr        if len  AfterArobase  not between 2 and 255 RETURN 0    --Controle sous-domain pas plus grand que 63 select top 1  HasDomainTooLong 1 from string split  AfterArobase       where LEN value  gt 63 if  HasDomainTooLong 1 return 0    --Control de la partie locale en detail SELECT  AlphabetPlus    abcdefghijklmnopqrstuvwxyz01234567890     amp      -                     Max   LEN  BeforeArobase          Pos   0         OK   1   WHILE  Pos  lt   Max AND  OK   1 BEGIN     SET  Pos    Pos   1     IF  AlphabetPlus NOT LIKE       SUBSTRING  BeforeArobase   Pos  1                 SET  OK   0 END  if  OK 0 RETURN 0    --Control de la partie domaine en detail SELECT  AlphabetPlus    abcdefghijklmnopqrstuvwxyz01234567890-           Max   LEN  AfterArobase          Pos   0         OK   1  WHILE  Pos  lt   Max AND  OK   1 BEGIN     SET  Pos    Pos   1     IF  AlphabetPlus NOT LIKE       SUBSTRING  AfterArobase   Pos  1                 SET  OK   0 END  if  OK 0 RETURN 0          return 1      END

User · Answer

SELECT Email FROM Employee WHERE NOT REGEXP LIKE Email      A-Z0-9    -    A-Z0-9 -     A-Z  2 4         i

User · Answer

Here is a quick and easy solution   CREATE FUNCTION dbo vaValidEmail  EMAIL varchar 100    RETURNS bit as BEGIN        DECLARE  bitRetVal as Bit   IF   EMAIL  lt  gt     AND  EMAIL NOT LIKE                    SET  bitRetVal   0  -- Invalid   ELSE      SET  bitRetVal   1   -- Valid   RETURN  bitRetVal END    Then you can find all rows by using the function   SELECT   FROM users WHERE dbo vaValidEmail email    0   If you are not happy with creating a function in your database  you can use the LIKE-clause directly in your query   SELECT   FROM users WHERE email NOT LIKE                Source

User · Answer

I propose my function    CREATE FUNCTION  REC   F IsEmail      EmailAddr varchar 360  -- Email address to check     RETURNS BIT -- 1 if  EmailAddr is a valid email address  AS BEGIN DECLARE  AlphabetPlus VARCHAR 255           Max INT -- Length of the address          Pos INT -- Position in  EmailAddr          OK BIT  -- Is  EmailAddr OK -- Check basic conditions IF  EmailAddr IS NULL     OR  EmailAddr NOT LIKE   0-9a-zA-Z                OR  EmailAddr LIKE             OR  EmailAddr LIKE            OR  EmailAddr LIKE           OR  EmailAddr LIKE           OR  EmailAddr LIKE      -       OR  EmailAddr LIKE     -        OR  EmailAddr LIKE    -       OR CHARINDEX     LTRIM RTRIM  EmailAddr     gt  0        RETURN 0     declare  AfterLastDot varchar 360   declare  AfterArobase varchar 360   declare  BeforeArobase varchar 360   declare  HasDomainTooLong bit 0   --Control des longueurs et autres incoherence set  AfterLastDot REVERSE SUBSTRING REVERSE  EmailAddr  0 CHARINDEX     REVERSE  EmailAddr      if  len  AfterLastDot  not between 2 and 17 RETURN 0    set  AfterArobase REVERSE SUBSTRING REVERSE  EmailAddr  0 CHARINDEX     REVERSE  EmailAddr      if len  AfterArobase  not between 2 and 255 RETURN 0    select top 1  BeforeArobase value from  string split  EmailAddr        if len  AfterArobase  not between 2 and 255 RETURN 0    --Controle sous-domain pas plus grand que 63 select top 1  HasDomainTooLong 1 from string split  AfterArobase       where LEN value  gt 63 if  HasDomainTooLong 1 return 0    --Control de la partie locale en detail SELECT  AlphabetPlus    abcdefghijklmnopqrstuvwxyz01234567890     amp      -                     Max   LEN  BeforeArobase          Pos   0         OK   1   WHILE  Pos  lt   Max AND  OK   1 BEGIN     SET  Pos    Pos   1     IF  AlphabetPlus NOT LIKE       SUBSTRING  BeforeArobase   Pos  1                 SET  OK   0 END  if  OK 0 RETURN 0    --Control de la partie domaine en detail SELECT  AlphabetPlus    abcdefghijklmnopqrstuvwxyz01234567890-           Max   LEN  AfterArobase          Pos   0         OK   1  WHILE  Pos  lt   Max AND  OK   1 BEGIN     SET  Pos    Pos   1     IF  AlphabetPlus NOT LIKE       SUBSTRING  AfterArobase   Pos  1                 SET  OK   0 END  if  OK 0 RETURN 0    return 1    END

[sql] Sql script to find invalid email addresses

Examples related to sql

Examples related to validation

Examples related to email