Sql script to find invalid email addresses

Question

Data was imported from the access database, and there was no confirmation in the email address field. Does anyone have a sql script that can return a list of invalid email addresses (missing @, etc.).

Thank!

Answer 1

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%' 

Anything more complex is likely to return false negatives and work more slowly.

Verifying email addresses in code is virtually impossible.

EDIT: Related Questions

• I answered a similar question a while ago: TSQL Validation (no regular expression)
• T-SQL: checking email format
• Does Regexp recognize email address hard?
• many other stack overflow questions

Answer 2

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 the lines 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 request:

 SELECT * FROM users WHERE email NOT LIKE '_%@__%.__%' 

Source

Answer 3

I find this simple T-SQL query useful for returning valid email addresses

 SELECT email FROM People WHERE email LIKE '%_@__%.__%' AND PATINDEX('%[^az,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0 

The PATINDEX bit eliminates all email addresses that contain characters that are not in the valid character sets az, 0-9, '@', '.', '_' And '-'.

You can do the opposite to do what you want:

 SELECT email FROM People WHERE NOT (email LIKE '%_@__%.__%' AND PATINDEX('%[^az,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0) 

Answer 4

MySQL

 SELECT * FROM `emails` WHERE `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|[az][az])|([0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}))(:[0-9]{1,5})?' 

Answer 5

 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 

It worked for me. I had to use rtrim and ltrim to avoid false positives.

Source: http://sevenwires.blogspot.com/2008/09/sql-how-to-find-invalid-email-in-sql.html

Version for Postgres:

 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 ; 

Answer 6

On sql server 2016 and above

 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 

Answer 7

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 invoke this using the following:

 SELECT [dbo].[ContainsVailidEmail] (Email) FROM [dbo].[User] 

OR

If you are going to use this only once, then why not do it as a calculated column with the following specification:

 (case when [Email] like '%_@__%.__%' then (1) else (0) end) 

Then you can simply use it without having to call a function.

Answer 8

I suggest 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 

Answer 9

 SELECT EmailAddress AS ValidEmail FROM Contacts WHERE EmailAddress LIKE '%_@__%.__%' AND PATINDEX('%[^az,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/

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

Answer 11

 select * from MailList.dbo.tblMailID where patindex ('%[ &'',":;!+=\/()<>]%', mailid) > 0 -- Invalid characters or patindex ('[@.-_]%', mailid) > 0 -- Valid but cannot be starting character or patindex ('%[@.-_]', mailid) > 0 -- Valid but cannot be ending character or mid not like '%@%.%' -- Must contain at least one @ and one . or mid like '%..%' -- Cannot have two periods in a row or mid like '%@%@%' -- Cannot have two @ anywhere or mid like '%.@%' or mailid like '%@.%' -- Cannot have @ and . next to each other or mid like '%.cm' or mailid like '%.co' -- Camaroon or Colombia? Unlikely. Probably typos or mid like '%.or' or mailid like '%.ne' -- Missing last letter 

Answer 12

 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 

Answer 13

I know the message is outdated, but after 3 months and with the various email combinations I came across, I can do this sql to check 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 welcome!

Answer 14

 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 the necessary extensions