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).

score 205 · Accepted Answer · edited May 23 '17 at 12:10

205

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

edited May 23 '17 at 12:10

Community

1
1

answered Apr 29 '09 at 06:53

Tomalak

332,285
67
532
628

5

I've used this one and it has not failed me in years. I consider myself pretty good at regexs but I think a cylon wrote this http://ex-parrot.com/~pdw/Mail-RFC822-Address.html – Chad Grant Apr 29 '09 at 07:12
1

Already too complex and wrong. foo@bar is a legal email address (providing the ".bar" TLD exists and has either an address or a MX record). – bortzmeyer Jun 16 '09 at 09:43
4

Calling this even "unlikely" would be very British already. The expression is not for validating e-mail addresses or checking every corner case. It is a basic sanity check that covers 99.9% of all cases without yielding false negatives, and I did not indicate otherwise. – Tomalak Jun 16 '09 at 11:18
Validating e-mail addresses with a regular expression (not for the faint of heart): http://www.regular-expressions.info/email.html (as you see, validating standard e-mail addresses is a LOT harder than validating common email addresses) – CMircea Mar 22 '10 at 06:34
foo@bar is going to crop up on the public Internet in the not too distant future too: http://www.canon.com/news/2010/mar16e.html – Quentin Mar 22 '10 at 06:35
Too simple. Missed all of our invalid addresses. – Antony Stubbs Feb 03 '12 at 21:25
1

This flags people with one letter domain emails, like johndoe@q.com which is valid (http://stockerblog.blogspot.com/2007/12/who-owns-one-letter-dot-com-domain.html). – pkr Aug 04 '14 at 20:15
@pkr That's trivial to change. I don't know if one letter domains were legal when this answer I wrote this answer. – Tomalak Aug 05 '14 at 05:20
3

Comments "too complex and wrong" followed by "Too simple" sums up all email validation nicely. This is a great sanity check expression that is exceptionally helpful in many circumstances. – toxaq Nov 02 '14 at 00:36
8

q.com is a popular email provider in the US. Might want to try `NOT LIKE '%_@%_.__%'` (with one character after the `@`) – jonaz Nov 01 '16 at 01:36
1

@jonaz Definitely. The decision to pic two characters was arbitrary and also reflects the age of the answer. Single letter domains in active use have been less common back then. Luckily it's an easy fix. – Tomalak Nov 01 '16 at 07:42
This answer is incorrect because it already returns false negatives. user@[IPv6:2001:db8::1] is a valid email address ... See https://stackoverflow.com/questions/2049502/what-characters-are-allowed-in-an-email-address – Stefan Steiger Aug 09 '18 at 08:52
Not good enough. Fails on single letter domains like xxx@c.dk which is a valid email address. – marlar Dec 14 '20 at 11:58
@marlar If you expect single-letter domains, change the pattern in your application. You're not required to use this answer exactly as it is. In any case, the comment from user toxaq a few lines further up still applies. – Tomalak Dec 14 '20 at 12:08
@Tomalak Thanks I know. Just wanted to pay attention to it in case other users implemented it without knowing. Didn't see the other comment before now though. – marlar Dec 15 '20 at 13:14
I really like the simplicity of this check, however I would like to emphasize that in my use case, it failed to identify the bulk of our invalid e-mail addresses: end users "concatenating" multiple email addresses in the field with separators like ";", ",". E.g. : "first.last@gmail.com;first2.last2@youpi.fr". I had to go for a more complex regex to cover those. – Pierre Massé Feb 08 '22 at 08:54
@PierreMassé You could catch those by adding `AND email NOT LIKE '%[ ;,<>()]%` (or any other combination of characters you consider an exclusion criterion). After all, the goal of the above check is not to actually validate email address, but to sort out obvious outliers. – Tomalak Feb 08 '22 at 08:58

Espo · Answer 2 · 2009-04-29T06:57:02.560

23

Here is a quick and easy solution:

CREATE FUNCTION dbo.vaValidEmail(@EMAIL varchar(100))

RETURNS bit as
BEGIN     
  DECLARE @bitRetVal as Bit
  IF (@EMAIL <> '' 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

edited Apr 29 '09 at 06:57

answered Apr 29 '09 at 06:51

Espo

41,399
21
132
159

I know this is old - when the email field is not populated this function yields a "1". There are some email domains that have 1 letter domains like x.com, q.com – joel Feb 20 '23 at 16:02

Jack Allan · Answer 3 · 2015-07-06T12:37:43.740

12

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, '@', '.', '_' & '-' 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)

edited Jul 06 '15 at 12:37

answered Jul 06 '15 at 12:32

Jack Allan

14,554
11
45
57

3

You can eliminate the REPLACE function by escaping the hyphen like this: `AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', email) = 0` – Splendor Mar 22 '17 at 17:19
@Splendor the 'escaped' hyphen results in back slash being allowed. I think the back slash should be removed. – Glen Thomas Dec 13 '21 at 11:07

score 7 · Answer 4 · edited Aug 05 '15 at 07:57

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 '%@.%' -- 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 '%[ &'',":;!+=\/()<>]%')) > 0  -- Invalid characters
or length(substring (email_address from '[@.-_]%')) > 0   -- Valid but cannot be starting character
or length(substring (email_address from '%[@.-_]')) > 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
;

@Manishm try PostgreSQL version with email `myname@gmail.`. this is why downvote from me - huge complexity but not working. — 1ac0, Nov 20 '14 at 07:15
Be carefule with domain names, because someone@domain.co.nz is valid (note the '.co' part) — kurdtpage, Apr 17 '16 at 08:57

score 4 · Answer 5 · edited Sep 14 '21 at 08:12

4

MySQL

SELECT * FROM `emails` WHERE lower(`email`)
NOT REGEXP '[-a-z0-9~!$%^&*_=+}{\\\'?]+(\\.[-a-z0-9~!$%^&*_=+}{\\\'?]+)*@([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})?'

edited Sep 14 '21 at 08:12

Community

1
1

answered Dec 13 '12 at 03:57

Zemistr

1,049
7
10

3

This doesn't work on MS SQL Server. Please specify DB vendor, on which the above syntax actually works. – Victor Zakharov Jan 28 '15 at 15:24

score 2 · Answer 6 · edited Nov 30 '18 at 17:09

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))) > 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)>63
if @HasDomainTooLong=1
return(0);

--Control de la partie locale en detail
SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890!#$%&‘*+-/=?^_`.{|}~'
     , @Max = LEN(@BeforeArobase)
     , @Pos = 0
     , @OK = 1


WHILE @Pos < @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 < @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

Phil C · Answer 7 · 2017-12-04T11:44:49.460

1

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.

edited Dec 04 '17 at 11:44

answered Dec 04 '17 at 11:31

Phil C

3,687
4
29
51

This doesn't check for special characters in the uri suffix: `select case when 'sample@email.com..%$%..' like '%_@__%.__%' then 1 else 0 end` returns 1 and it should return 0. – gbeaven Sep 12 '22 at 21:25

score 1 · Answer 8 · edited Oct 23 '19 at 07:06

1

SELECT Email FROM Employee WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

edited Oct 23 '19 at 07:06

Joakim Skoog

766
10
23

answered Oct 23 '19 at 06:48

Blck Devine

21
1

4

You can add an explanation as to what your answer does and why you did it that way. Having only-code answers is a bit confusing. – mnestorov Oct 23 '19 at 07:18

score 1 · Answer 9 · answered Sep 30 '22 at 10:07

DECLARE @Email Varchar(50)
SET @Email='rbaviskar@gmail.com'

SELECT CONCAT(LEFT(@Email, 3), REPLICATE('X', CHARINDEX('@',@Email) - 4),
            SUBSTRING(@Email, CHARINDEX('@',@Email), 1), 
            REPLICATE('X', CHARINDEX('.',@Email) - CHARINDEX('@',@Email) - 1),
            SUBSTRING(@Email, CHARINDEX('.',@Email), 1),            
            PARSENAME(@Email, 1))

O/p : rbaXXXXXX@XXXXX.com

score 0 · Answer 10 · answered Dec 31 '17 at 11:29

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))) > 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)>63
if @HasDomainTooLong=1
return(0);

--Control de la partie locale en detail
SELECT @AlphabetPlus = 'abcdefghijklmnopqrstuvwxyz01234567890!#$%&‘*+-/=?^_`.{|}~'
     , @Max = LEN(@BeforeArobase)
     , @Pos = 0
     , @OK = 1


WHILE @Pos < @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 < @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

score 0 · Answer 11 · edited Feb 06 '19 at 12:30

0

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/

edited Feb 06 '19 at 12:30

Raduan Santos

1,023
1
21
45

answered Feb 06 '19 at 11:16

rino

21
3

score 0 · Answer 12 · edited Feb 14 '19 at 02:25

0

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.

edited Feb 14 '19 at 02:25

Vasyl Moskalov

4,242
3
20
28

answered Feb 14 '19 at 01:28

Kumar Ankur

11
2

score -1 · Answer 13 · edited Jul 03 '17 at 15:23

-1

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

edited Jul 03 '17 at 15:23

Bugs

4,491
9
32
41

answered Jul 03 '17 at 10:31

B. VENKA REDDY

1

score -1 · Answer 14 · edited Feb 03 '12 at 23:10

select * from users 
WHERE NOT
(     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)

score -2 · Answer 15 · answered Oct 28 '14 at 14:46

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) <> '@'
    AND RIGHT(RTRIM(@EmailAddress),1) <> '.'
    AND LEFT(LTRIM(@EmailAddress),1) <> '-'
    AND CHARINDEX('.',@EmailAddress,CHARINDEX('@',@EmailAddress)) - CHARINDEX('@',@EmailAddress) > 2    
    AND LEN(LTRIM(RTRIM(@EmailAddress))) - LEN(REPLACE(LTRIM(RTRIM(@EmailAddress)),'@','')) = 1
    AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@EmailAddress)))) >= 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('&', @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('<', @EmailAddress)=0
    AND CHARINDEX('>', @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)))>1
    AND CHARINDEX('.', REVERSE(@EmailAddress)) > 2
    AND CHARINDEX('.', REVERSE(@EmailAddress)) < 5  
    THEN 1 ELSE  0 END


    RETURN @Result
END

Any suggestions are welcomed!

Sql script to find invalid email addresses

15 Answers15

Linked