Ok, there are a million regexes out there for validating an email address, but how about some basic email validation that can be integrated into a TSQL query for Sql Server 2005?
I don't want to use a CLR procedure or function. Just straight TSQL.
Has anybody tackled this already?
Very basic would be:
SELECT
EmailAddress,
CASE WHEN EmailAddress LIKE '%_@_%_.__%'
AND EmailAddress NOT LIKE '%[any obviously invalid characters]%'
THEN 'Could be'
ELSE 'Nope'
END Validates
FROM
Table
This matches everything with an @ in the middle, preceded by at least one character, followed by at least two, a dot and at least two for the TLD.
You can write more LIKE patterns that do more specific things, but you will never be able to match everything that could be an e-mail address while not letting slip through things that are not. Even with regular expressions you have a hard time doing it right. Additionally, even matching according to the very letters of the RFC matches address constructs that will not be accepted/used by most emailing systems.
Doing this on the database level is maybe the wrong approach anyway, so a basic sanity check as indicated above may be the best you can get performance-wise, and doing it in an application will provide you with far greater flexibility.
Here's a sample function for this that's a little more detailed, I don't remember where I got this from (years ago), or if I modified it, otherwise I would include proper attribution:
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
AND CHARINDEX(' ',@email) = 0 in order to check for spaces in the middle of the email address.
Great answers! Based these recommendations I came up with a simplified function that combines the best 2 answers.
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
On SQL 2016 or +
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
FnAppStripNonEmail missing under score, need add it to the keep values
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
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
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
This is the easiest way to select them.
Use this query
SELECT * FROM <TableName> WHERE [EMail] NOT LIKE '%_@__%.__%'
From sql 2008 onwards patindex removes repetitive looksups
CREATE FUNCTION ValidateEmail (@Email VARCHAR(100))
RETURNS VARCHAR(3)
AS
BEGIN
RETURN (
SELECT CASE
WHEN ISNULL(@Email, '') = '' THEN 'No'
WHEN @Email LIKE '% %' THEN 'No'
WHEN PATINDEX('%["(),:;<>]%', @Email) <> 0 THEN 'No'
WHEN SUBSTRING(@Email, CHARINDEX('@', @Email), LEN(@Email)) LIKE ('%[!#$%&*+/=?^`_{|]%') THEN 'No'
WHEN (LEFT(@Email, 1) LIKE ('[-_.+]') OR RIGHT(@Email, 1) LIKE ('[-_.+]')) THEN 'No'
WHEN (@Email LIKE '%[%' OR @Email LIKE '%]%') THEN 'No'
WHEN @Email LIKE '%@%@%' OR @Email LIKE '%@%..%' THEN 'No'
WHEN PATINDEX('_%@_%.__%', @Email) = 0 THEN 'No'
ELSE 'Yes'
END
)
END;
This also validates that email starts with a character, which many others miss.
Although personal i'd use
trim(@Email) = '' THEN 'No' rather than @Email LIKE '% %' THEN 'No'
From Tomalak's slelect
select 1
where @email not like '%[^a-z,0-9,@,.]%'
and @email like '%_@_%_.__%'
