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)