sql-server - validacion - validate email sql function

Validación de correo electrónico de TSQL(sin regex)

Question

De acuerdo, hay un millón de expresiones regulares para validar una dirección de correo electrónico, pero ¿qué tal una validación de correo electrónico básica que se puede integrar en una consulta TSQL para Sql Server 2005?

No quiero usar un procedimiento o función CLR. Justo derecho TSQL.

¿Alguien ha abordado esto ya?

Answer 1

¡Excelentes respuestas! Basado en estas recomendaciones, se me ocurrió una función simplificada que combina las mejores 2 respuestas.

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

Answer 2

Aquí hay una función de muestra que es un poco más detallada, no recuerdo de dónde saqué esto (hace años), o si lo modifiqué, de lo contrario incluiría la atribución adecuada:

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

Answer 3

Del slelect de Tomalak

select 1
where @email not like ''%[^a-z,0-9,@,.]%''
and @email like ''%_@_%_.__%''

Answer 4

En SQL 2016 o +

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 5

Esta es la forma más fácil de seleccionarlos.

Use esta consulta

SELECT * FROM <TableName> WHERE [EMail] NOT LIKE ''%_@__%.__%''

Answer 6

FnAppStripNonEmail falta por debajo del puntaje, es necesario agregarlo a los valores de mantener

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

Answer 7

Muy básico sería:

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

Esto combina todo con un @ en el medio, precedido por al menos un personaje, seguido de al menos dos, un punto y al menos dos para el TLD.

Puede escribir más patrones LIKE que hagan cosas más específicas, pero nunca podrá hacer coincidir todo lo que podría ser una dirección de correo electrónico, sin dejar pasar cosas que no lo son. Incluso con expresiones regulares, es difícil hacerlo bien. Además, incluso el emparejamiento de acuerdo con las mismas letras del RFC coincide con los constructos de direcciones que no serán aceptados / usados ​​por la mayoría de los sistemas de envío de correos electrónicos.

Hacer esto en el nivel de la base de datos es tal vez el enfoque equivocado de todos modos, por lo que un control de cordura básico como se indicó anteriormente puede ser lo mejor que puede obtener en cuanto a rendimiento, y hacerlo en una aplicación le proporcionará una flexibilidad mucho mayor.

Answer 8

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

Answer 9

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