Sql script para encontrar direcciones de correo electrónico no válidas

Se realizó una import de datos desde una database de acceso y no hubo validation en el campo de la dirección de correo electrónico. ¿Alguien tiene un script sql que puede devolver una list de direcciones de correo electrónico no válidas (falta @, etc.)?

¡Gracias!

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

Cualquier cosa más compleja probablemente arrojará falsos negativos y correrá más despacio.

Validar las direcciones de correo electrónico en el código es prácticamente imposible.

EDIT: preguntas relacionadas

  • He respondido una pregunta similar hace algún time: Validación de correo electrónico de TSQL (sin expresiones regulares)
  • T-SQL: verificar el formatting del correo electrónico
  • Reconocimiento Regexp de la dirección de correo electrónico difícil?
  • muchas otras preguntas de desbordamiento de stack

Aquí hay una solución rápida y fácil:

 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 

Luego puedes encontrar todas las filas usando la function:

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

Si no está satisfecho con la creación de una function en su database, puede usar la cláusula LIKE directamente en su consulta:

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

Fuente

Encuentro que esta simple consulta T-SQL es útil para devolver direcciones de correo electrónico válidas

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

El bit PATINDEX elimina todas las direcciones de correo electrónico que contienen caracteres que no están en el set de caracteres permitido az, 0-9, '@', '.', '_' Y '-'.

Se puede revertir para hacer lo que quieras así:

 SELECT email FROM People WHERE NOT (email LIKE '%_@__%.__%' 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 '%.@%' 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 

Esto funcionó para mí. Tuvo que aplicar rtrim y ltrim para evitar falsos positivos.

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

Versión de 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 ; 

En el server sql 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 '%.@' 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 

Encuentro este enfoque más intuitivo:

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

Lo llamo usando lo siguiente:

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

O

Si solo va a usar esto una vez, ¿por qué no hacerlo como una Columna computada, con la siguiente especificación:

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

Entonces puede usarlo sin necesidad de llamar a una function.

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

Sé que la publicación es antigua, pero después de 3 meses y con varias combinaciones de correo electrónico me encontré con la posibilidad de crear este sql para validar las ID de correo electrónico.

 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 

¡Cualquier sugerencia es bienvenida!

 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" 

y pon toda la extensión que quieras

    Intereting Posts