Script Sql per trovare indirizzi email non validi

Un’importazione di dati è stata effettuata da un database di accesso e non è stata eseguita alcuna convalida nel campo dell’indirizzo di posta elettronica. Qualcuno ha uno script sql che può restituire un elenco di indirizzi email non validi (mancante @, ecc.).

Grazie!

SELECT * FROM people WHERE email NOT LIKE '%[email protected]__%.__%' 

Qualunque cosa più complessa probabilmente restituirà dei falsi negativi e rallenterà.

Convalidare gli indirizzi e-mail nel codice è praticamente imansible.

EDIT: domande correlate

  • Ho risposto a una domanda simile qualche tempo fa: TSQL Email Validation (senza regex)
  • T-SQL: verifica del formato email
  • Riconoscimento difficile dell’indirizzo email?
  • molte altre domande di overflow dello stack

Ecco una soluzione rapida e semplice:

 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 

Quindi puoi trovare tutte le righe usando la funzione:

 SELECT * FROM users WHERE dbo.vaValidEmail(email) = 0 

Se non sei soddisfatto della creazione di una funzione nel tuo database, puoi utilizzare la clausola LIKE direttamente nella tua query:

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

fonte

Trovo questa semplice query T-SQL utile per la restituzione di indirizzi e-mail validi

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

Il bit PATINDEX elimina tutti gli indirizzi e-mail contenenti caratteri che non sono nel gruppo az, 0-9, ‘@’, ‘.’, ‘_’ E ‘-‘ dei caratteri consentiti.

Può essere invertito per fare ciò che vuoi in questo modo:

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

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})?' 
 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 '%[email protected]%' 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 

Questo ha funzionato per me. Dovevo applicare rtrim e ltrim per evitare falsi positivi.

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

Versione di 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 '%[email protected]%' 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 ; 

Su sql server 2016 o sup

 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 '%[email protected]' 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 

Trovo questo approccio più intuitivo:

 CREATE FUNCTION [dbo].[ContainsVailidEmail] (@Input varchar(250)) RETURNS bit AS BEGIN RETURN CASE WHEN @Input LIKE '%[email protected]__%.__%' THEN 1 ELSE 0 END END 

Lo chiamo usando il seguente:

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

O

Se lo userai solo una volta, perché non farlo come una colonna calcasting, con le seguenti specifiche:

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

Quindi puoi semplicemente usarlo senza dover chiamare una funzione.

Propongo la mia funzione:

 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 '%[email protected]' 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 
 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 protected]',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) 
 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 '%[email protected]%' 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 
 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 

So che il post è vecchio ma dopo un periodo di 3 mesi e con varie combinazioni di email mi sono imbattuto in, in grado di rendere questo sql per la convalida degli ID email.

 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('[email protected]',@EmailAddress) = 0 AND CHARINDEX('..',@EmailAddress) = 0) AND (CHARINDEX('[email protected]',@EmailAddress) = 0 AND CHARINDEX('..',@EmailAddress) = 0) AND (CHARINDEX('[email protected]',@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 

Qualsiasi suggerimento è ben accetto!

 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" 

e metti tutta l’estensione che vuoi