Script Sql pour trouver les adresses e-mail invalides

Une importation des données a été fait à partir d'une base de données access et il n'y a pas de validation sur le champ adresse e-mail. Quelqu'un aurait-il un script sql qui peut retourner une liste d'adresses e-mail invalides (manquant @, etc).

Merci!

InformationsquelleAutor campo | 2009-04-29
  1. 141
    SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

    Quelque chose de plus complexe sera probablement de retour de faux négatifs et de les exécuter plus lentement.

    La validation des adresses e-mail dans le code est pratiquement impossible.

    EDIT: questions Liées à

    • J'ai utilisé celui-ci et il n'a pas manqué de me dans années. Je me considère très bon à l'regexs, mais je pense qu'un cylone écrit ce ex-parrot.com/~pdw/Mail-RFC822-Address.html
    • Merci, c'est génial! J'ai fait une recherche de stackoverflow mais j'aurais dû utiliser la recherche google :/ Cheers!
    • Déjà trop complexe et mal. toto@bar juridique est une adresse de courriel (à condition que l' ".bar" TLD existe et a une adresse ou d'un enregistrement MX).
    • L'appel de cette même "peu probable" serait très British déjà. L'expression n'est pas de la validation des adresses e-mail ou la vérification de tous les cas de coin. C'est une base de test de cohérence qui couvre 99,9% de tous les cas sans céder de faux négatifs, et je n'ai pas indiqué autrement.
    • La validation des adresses e-mail avec une expression régulière (pas pour les faibles de cœur): regular-expressions.info/email.html comme vous le voyez, la validation de la norme e-mail est BEAUCOUP plus difficile que la validation des communes adresses e-mail)
    • toto@bar va de rogner sur l'Internet public dans un avenir pas trop lointain, trop: canon.com/news/2010/mar16e.html
    • Trop simple. Raté toutes nos adresses non valides.
    • Ce drapeaux des personnes avec une lettre de domaine, e-mails, comme [email protected] ce qui est valable (stockerblog.blogspot.com/2007/12/...).
    • C'est trivial pour le changement. Je ne sais pas si une lettre de domaines étaient juridique lorsque cette réponse, j'ai écrit cette réponse.
    • Commentaires "trop complexe et mal", suivi par "Trop simple", résume tous les e-mail de validation de bien. C'est un grand test de cohérence de l'expression qui est extrêmement utile dans de nombreuses circonstances.
    • q.com populaire est un fournisseur de messagerie en NOUS. Voudrez peut-être essayer NOT LIKE '%_@%_.__%' (avec un caractère après l' @)
    • Certainement. La décision de la pic de deux personnages était arbitraire et reflète également l'âge de la réponse. Une seule lettre les domaines de l'activité ont été moins bien connus. Heureusement, c'est une solution facile.
    • Cette réponse est incorrecte car elle renvoie déjà des faux négatifs. utilisateur@[IPv6:2001:db8::1] est une adresse email valide ... Voir stackoverflow.com/questions/2049502/...

    InformationsquelleAutor Tomalak
  2. 19

    Voici une solution simple et rapide:

    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

    Alors vous pouvez trouver toutes les lignes à l'aide de la fonction:

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

    Si vous n'êtes pas heureux avec la création d'une fonction dans votre base de données, vous pouvez utiliser la clause directement dans votre requête:

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

    Source

    • +1 pour l'UDF.
    InformationsquelleAutor Espo
  3. 6

    Je trouve cette simple requête T-SQL utile pour le retour des adresses électroniques valides

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

    La PATINDEX peu élimine toutes les adresses e-mail contenant des caractères qui ne sont pas dans le permis a-z, 0-9, '@', '.', '_' & '-' le jeu de caractères.

    Il peut être inversé pour faire ce que vous voulez comme ceci:

    SELECT email
FROM People
WHERE NOT (email LIKE '%_@__%.__%' 
    AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0)
    • Vous pouvez éliminer la fonction REMPLACER par s'échapper le trait d'union comme ceci: AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', email) = 0
    InformationsquelleAutor Jack Allan
  4. 3

    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})?'
    • Cela ne fonctionne pas sur MS SQL Server. Veuillez préciser DB fournisseur, sur lequel la syntaxe ci-dessus fonctionne réellement.
    InformationsquelleAutor Zemistr
  5. 2
    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

    Cela a fonctionné pour moi. A appliquer rtrim et ltrim pour éviter les faux positifs.

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

    Postgres version:

    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
;
    • essayez la version de PostgreSQL avec e-mail myname@gmail.. c'est pourquoi downvote de moi - énorme complexité, mais ne fonctionne pas.
    • [email protected] est un mail valide l'adresse
    • Attention avec les noms de domaine, parce que [email protected] est valide (notez le".co' partie)
    • .-_ sont valables départ caractères
    InformationsquelleAutor
  6. 1

    Je trouve cette approche plus intuitive:

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

    Je l'appelle à l'aide les suivantes:

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

    OU

    Si vous allez seulement utiliser cette fois, alors pourquoi pas comme une Colonne Calculée, avec les spécifications suivantes:

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

    Alors vous pouvez simplement l'utiliser sans avoir besoin d'appeler une fonction.

    InformationsquelleAutor CarneyCode
  7. 1

    Sur sql server 2016 et jusqu'

    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
    InformationsquelleAutor Esperento57
  8. 0

    Je propose ma fonction :

    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
    InformationsquelleAutor Esperento57
  9. 0
    SELECT EmailAddress AS ValidEmail
FROM Contacts
WHERE EmailAddress LIKE '%_@__%.__%'
AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', EmailAddress) = 0
GO

    Veuillez consulter ce lien: https://blog.sqlauthority.com/2017/11/12/validate-email-address-sql-server-interview-question-week-147/

    InformationsquelleAutor rino
  10. 0
    sel 'unismankur@yahoo#.co.in' as Email, 
case 
when Email not like  '%@xx%' 
AND  Email like  '%@%' 
AND  CHAR_LENGTH(
oTranslate(
trim( Email),
'._-@0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'')
) = 0
then 'N' else 'Y'  end as Invalid_Email_Ind;

    Cela fonctionne très bien pour moi.

    InformationsquelleAutor Kumar Ankur
  11. -1
    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)
    InformationsquelleAutor
  12. -1
    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
    InformationsquelleAutor Shoaib
  13. -1
    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
    • Veuillez formater votre code et d'ajouter une description!!
    InformationsquelleAutor B. VENKA REDDY
  14. -2

    Je sais que le post est vieux mais après 3 mois de temps et avec divers e-mail combinaisons je suis venu à travers, de pouvoir faire ce sql pour la validation de l'Email Id.

    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

    Toutes les suggestions sont les bienvenues!

    InformationsquelleAutor anurag_it
  15. -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"

    et de mettre toute l'extension que vous souhaitez

    • Merci de modifier votre réponse et de formater le code afin de le rendre lisible.
    • Grand. Cela permettrait de supprimer toutes les adresses e-mail valides de l'Autriche, du Liechtenstein ou d'autres Tld. (Ne peut pas downvote, n'ont pas assez de réputation).
    InformationsquelleAutor user2199272