用于查找无效电子邮件地址的 Sql 脚本

数据导入是从一个访问数据库完成的，并且没有对电子邮件地址字段进行验证。有没有人有一个 sql 脚本，可以返回一个无效的电子邮件地址列表(缺少@等)。

240971 次浏览
小开

Here is a quick and easy solution:

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

Then you can find all rows by using the function:

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

If you are not happy with creating a function in your database, you can use the LIKE-clause directly in your query:

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

Source

小开
最佳答案 
SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'

Anything more complex will likely return false negatives and run slower.

Validating e-mail addresses in code is virtually impossible.

EDIT: Related questions

小开 
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)
小开 
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

This worked for me. Had to apply rtrim and ltrim to avoid false positives.

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
;
小开

MySQL

SELECT * FROM `emails` WHERE lower(`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})?'
小开

I know the post is old but after a 3 months time and with various email combinations I came across, able to make this sql for validating Email IDs.

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

Any suggestions are welcomed!

小开

I find this simple T-SQL query useful for returning valid e-mail addresses

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

The PATINDEX bit eliminates all e-mail addresses containing characters that are not in the allowed a-z, 0-9, '@', '.', '_' & '-' set of characters.

It can be reversed to do what you want like this:

SELECT email
FROM People
WHERE NOT (email LIKE '%_@__%.__%'
AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0)
小开 
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
小开

On sql server 2016 and up

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
小开

I find this approach more intuitive:

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

I call it using the following:

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

OR

If you are only going to use this once then why not it as a Computed Column, with the following specification:

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

Then you can just use it without needing to call a function.

小开

I propose my function :

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

Please check this link: https://blog.sqlauthority.com/2017/11/12/validate-email-address-sql-server-interview-question-week-147/

小开 
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;

This works very well for me.

小开 
SELECT Email FROM Employee WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);
小开 
DECLARE @Email Varchar(50)
SET @Email='rbaviskar@gmail.com'


SELECT CONCAT(LEFT(@Email, 3), REPLICATE('X', CHARINDEX('@',@Email) - 4),
SUBSTRING(@Email, CHARINDEX('@',@Email), 1),
REPLICATE('X', CHARINDEX('.',@Email) - CHARINDEX('@',@Email) - 1),
SUBSTRING(@Email, CHARINDEX('.',@Email), 1),
PARSENAME(@Email, 1))


O/p : rbaXXXXXX@XXXXX.com

提交答案