Validation de L'Email TSQL (sans regex)

Ok, il y a un million de regexes là-bas pour valider une adresse email, mais que diriez-vous d'une validation email de base qui peut être intégrée dans une requête TSQL pour Sql Server 2005?

Je ne veux pas utiliser une procédure ou une fonction CLR. Tout droit TSQL.

quelqu'un s'est déjà attaqué à ça?

27
demandé sur Eric Z Beard 2008-10-23 18:00:19

9 réponses

Très de base serait:

SELECT
  EmailAddress, 
  CASE WHEN EmailAddress LIKE '%_@_%_.__%' 
            AND EmailAddress NOT LIKE '%[any obviously invalid characters]%' 
  THEN 'Could be' 
  ELSE 'Nope' 
  END Validates
FROM 
  Table

cela correspond à tout avec un @ au milieu, précédé d'au moins un caractère, suivi d'au moins deux, un point et au moins deux pour le TLD.

vous pouvez écrire plus LIKE modèles qui font des choses plus spécifiques, mais vous ne serez jamais en mesure de faire correspondre tout ce qui pourrait être une adresse e-mail tout en ne laissant glisser à travers les choses qui ne sont pas. Même régulièrement expressions vous avez un moment difficile de le faire à droite. De plus, même l'appariement selon les lettres mêmes de la RFC correspond à des constructions d'adresses qui ne seront pas acceptées/utilisées par la plupart des systèmes de messagerie.

faire cela au niveau de la base de données est peut-être la mauvaise approche de toute façon, donc un contrôle de base de la santé mentale comme indiqué ci-dessus peut être le meilleur que vous pouvez obtenir la performance-sage, et le faire dans une application vous fournira beaucoup plus de flexibilité.

45
répondu Tomalak 2008-10-23 14:40:04

voici un exemple de fonction pour ceci qui est un peu plus détaillé, Je ne me souviens pas d'où je l'ai obtenu de (Il y a des années), ou si je l'ai modifié, sinon j'inclurais l'attribution correcte:

CREATE FUNCTION [dbo].[fnAppEmailCheck](@email VARCHAR(255))   
--Returns true if the string is a valid email address.  
RETURNS bit  
as  
BEGIN  
     DECLARE @valid bit  
     IF @email IS NOT NULL   
          SET @email = LOWER(@email)  
          SET @valid = 0  
          IF @email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%'  
             AND LEN(@email) = LEN(dbo.fnAppStripNonEmail(@email))  
             AND @email NOT like '%@%@%'  
             AND CHARINDEX('.@',@email) = 0  
             AND CHARINDEX('..',@email) = 0  
             AND CHARINDEX(',',@email) = 0  
             AND RIGHT(@email,1) between 'a' AND 'z'  
               SET @valid=1  
     RETURN @valid  
END  
19
répondu cabgef 2009-08-28 07:28:40

de Grandes réponses! En me basant sur ces recommandations, j'ai trouvé une fonction simplifiée qui combine les deux meilleures réponses.

CREATE FUNCTION [dbo].[fnIsValidEmail]
(
    @email varchar(255)
)   
--Returns true if the string is a valid email address.  
RETURNS bit  
As  
BEGIN
    RETURN CASE WHEN ISNULL(@email, '') <> '' AND @email LIKE '%_@%_.__%' THEN 1 ELSE 0 END
END
4
répondu James 2016-05-27 19:47:44

FnAppStripNonEmail manquant sous score, il faut l'Ajouter aux valeurs de conservation

Create Function [dbo].[fnAppStripNonEmail](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z,0-9,_,@,.,-]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End
1
répondu Tony Dong 2016-02-02 17:39:51
Create Function [dbo].[fnAppStripNonEmail](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z,0-9,@,.,-]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End
0
répondu Mike Wallace 2014-09-22 16:55:12

C'est la façon la plus facile de les sélectionner.

utilisez cette requête

SELECT * FROM <TableName> WHERE [EMail] NOT LIKE '%_@__%.__%'
0
répondu HamzeLue 2015-03-10 11:43:42
CREATE FUNCTION fnIsValidEmail
(
    @email varchar(255)
)
RETURNS bit
AS
BEGIN

    DECLARE @IsValidEmail bit = 0

    IF (@email not like '%[^a-z,0-9,@,.,!,#,$,%%,&,'',*,+,--,/,=,?,^,_,`,{,|,},~]%' --First Carat ^ means Not these characters in the LIKE clause. The list is the valid email characters.
        AND @email like '%_@_%_.[a-z,0-9][a-z]%'
        AND @email NOT like '%@%@%'  
        AND @email NOT like '%..%'
        AND @email NOT like '.%'
        AND @email NOT like '%.'
        AND CHARINDEX('@', @email) <= 65
        )
    BEGIN
        SET @IsValidEmail = 1
    END

    RETURN @IsValidEmail

END
0
répondu Roy Gelerman 2017-04-24 14:13:32

sur SQL 2016 ou +

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
0
répondu Esperento57 2017-11-17 12:35:56

De Tomalak de slelect

select 1
where @email not like '%[^a-z,0-9,@,.]%'
and @email like '%_@_%_.__%'
-2
répondu payonk 2010-05-11 10:19:00