job - enviar resultado query por email sql server

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

Question

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

¡Gracias!

Answer 1

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 función:

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

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

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

Source

Answer 2

En el servidor 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

Answer 3

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 utilizar 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 función.

Answer 4

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(''%[^a-z,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 conjunto 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(''%[^a-z,0-9,@,.,_]%'', REPLACE(email, ''-'', ''a'')) = 0)

Answer 5

Propongo mi función:

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 6

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!

Answer 7

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|[a-z][a-z])|([0-9]{1,3}//.[0-9]{1,3}//.[0-9]{1,3}//.[0-9]{1,3}))(:[0-9]{1,5})?''

Answer 8

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

Answer 9

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 tiempo: Validación de correo electrónico de TSQL (sin expresiones regulares)
• T-SQL: verificar el formato del correo electrónico
• Reconocimiento Regexp de la dirección de correo electrónico difícil?
• muchas otras preguntas de desbordamiento de pila

Answer 10

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 11

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
;

Answer 12

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 13

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)