用于查找无效电子邮件地址的 Sql 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/801166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql script to find invalid email addresses
提问by campo
A data import was done from an access database and there was no validation on the email address field. Does anyone have an sql script that can return a list of invalid email addresses (missing @, etc).
数据导入是从访问数据库完成的，并且没有对电子邮件地址字段进行验证。有没有人有可以返回无效电子邮件地址列表（缺少@ 等）的 sql 脚本。
回答by Tomalak
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
编辑：相关问题
- I've answered a similar question some time ago: TSQL Email Validation (without regex)
- T-SQL: checking for email format
- Regexp recognition of email address hard?
- many other Stack Overflow questions
回答by Espo
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:
如果您对在数据库中创建函数不满意，可以直接在查询中使用 LIKE 子句：
SELECT * FROM users WHERE email NOT LIKE '_%@__%.__%'
回答by Hyman Allan
I find this simple T-SQL query useful for returning valid e-mail addresses
我发现这个简单的 T-SQL 查询对于返回有效的电子邮件地址很有用
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.
PATINDEX 位消除了所有包含不在允许的 az、0-9、'@'、'.'、'_' 和 '-' 字符集中的字符的电子邮件地址。
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)
回答by Hyman Allan
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.
这对我有用。必须应用 rtrim 和 ltrim 以避免误报。
Source: http://sevenwires.blogspot.com/2008/09/sql-how-to-find-invalid-email-in-sql.html
来源：http: //sevenwires.blogspot.com/2008/09/sql-how-to-find-invalid-email-in-sql.html
Postgres version:
Postgres 版本：
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
;
回答by Zemistr
MySQL
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})?'
回答by Esperento57
On sql server 2016 and up
在 sql server 2016 及更高版本上
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
回答by CarneyCode
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.
然后你可以直接使用它而无需调用函数。
回答by Esperento57
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
回答by rino
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/
请查看此链接：https: //blog.sqlauthority.com/2017/11/12/validate-email-address-sql-server-interview-question-week-147/
回答by Kumar Ankur
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.
这对我来说非常有效。
