반응형
유효하지 않은 이메일 주소를 찾기위한 SQL 스크립트
액세스 데이터베이스에서 데이터 가져 오기가 수행되었으며 이메일 주소 필드에 대한 유효성 검증이 없습니다. 잘못된 이메일 주소 목록 (@ 누락 등)을 반환 할 수있는 SQL 스크립트가있는 사람이 있습니까?
감사!
SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'
더 복잡한 것은 거짓 부정을 반환하고 느리게 실행될 수 있습니다.
코드에서 이메일 주소의 유효성을 검사하는 것은 사실상 불가능합니다.
편집 : 관련 질문
- 얼마 전에 비슷한 질문에 대답했습니다. TSQL 이메일 유효성 검사 (정규식 없음)
- T-SQL : 이메일 형식 확인
- 정규식 이메일 주소 인식이 어렵습니까?
- 다른 많은 스택 오버플로 질문
다음은 빠르고 쉬운 솔루션입니다.
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
그런 다음 함수를 사용하여 모든 행을 찾을 수 있습니다.
SELECT * FROM users WHERE dbo.vaValidEmail(email) = 0
데이터베이스에서 함수를 만드는 것이 만족스럽지 않은 경우 쿼리에서 직접 LIKE 절을 사용할 수 있습니다.
SELECT * FROM users WHERE email NOT LIKE '_%@__%.__%'
이 간단한 T-SQL 쿼리는 유효한 전자 메일 주소를 반환하는 데 유용합니다.
SELECT email
FROM People
WHERE email LIKE '%_@__%.__%'
AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0
PATINDEX 비트는 허용 된 az, 0-9, '@', '.', '_'및 '-'문자 집합에없는 문자를 포함하는 모든 전자 메일 주소를 제거합니다.
다음과 같이 원하는 작업을 되돌릴 수 있습니다.
SELECT email
FROM People
WHERE NOT (email LIKE '%_@__%.__%'
AND PATINDEX('%[^a-z,0-9,@,.,_]%', REPLACE(email, '-', 'a')) = 0)
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})?'
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
이것은 나를 위해 일했습니다. 오탐을 피하기 위해 rtrim과 ltrim을 적용해야했습니다.
출처 : http://sevenwires.blogspot.com/2008/09/sql-how-to-find-invalid-email-in-sql.html
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
;
이 접근 방식이 더 직관적이라고 생각합니다.
CREATE FUNCTION [dbo].[ContainsVailidEmail] (@Input varchar(250))
RETURNS bit
AS
BEGIN
RETURN CASE
WHEN @Input LIKE '%_@__%.__%' THEN 1
ELSE 0
END
END
다음을 사용하여 호출합니다.
SELECT [dbo].[ContainsVailidEmail] (Email) FROM [dbo].[User]
또는
이것을 한 번만 사용하려는 경우 다음 사양을 사용하여 계산 된 열로 사용하지 않는 이유는 무엇입니까?
(case when [Email] like '%_@__%.__%' then (1) else (0) end)
그러면 함수를 호출하지 않고도 사용할 수 있습니다.
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
내 기능을 제안합니다.
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
이 링크를 확인하십시오 : 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;
이것은 나를 위해 아주 잘 작동합니다.
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 *
from MailList.dbo.tblMailID
where
patindex ('%[ &'',":;!+=\/()<>]%', mailid) > 0 -- Invalid characters
or patindex ('[@.-_]%', mailid) > 0 -- Valid but cannot be starting character
or patindex ('%[@.-_]', mailid) > 0 -- Valid but cannot be ending character
or mid not like '%@%.%' -- Must contain at least one @ and one .
or mid like '%..%' -- Cannot have two periods in a row
or mid like '%@%@%' -- Cannot have two @ anywhere
or mid like '%.@%' or mailid like '%@.%' -- Cannot have @ and . next to each other
or mid like '%.cm' or mailid like '%.co' -- Camaroon or Colombia? Unlikely. Probably typos
or mid like '%.or' or mailid like '%.ne' -- Missing last letter
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
나는 게시물이 오래되었다는 것을 알고 있지만 3 개월 후 다양한 이메일 조합을 통해 이메일 ID 유효성 검사를 위해이 SQL을 만들 수있었습니다.
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
어떤 제안이라도 환영합니다!
DELETE
FROM `contatti`
WHERE `EMail` NOT LIKE "%.it"
AND `EMail` NOT LIKE "%.com"
AND `EMail` NOT LIKE "%.fr"
AND `EMail` NOT LIKE "%.net"
AND `EMail` NOT LIKE "%.ru"
AND `EMail` NOT LIKE "%.eu"
AND `EMail` NOT LIKE "%.org"
AND `EMail` NOT LIKE "%.edu"
AND `EMail` NOT LIKE "%.uk"
AND `EMail` NOT LIKE "%.de"
AND `EMail` NOT LIKE "%.biz"
AND `EMail` NOT LIKE "%.ch"
AND `EMail` NOT LIKE "%.bg"
AND `EMail` NOT LIKE "%.info"
AND `EMail` NOT LIKE "%.br"
AND `EMail` NOT LIKE "%.pt"
AND `EMail` NOT LIKE "%.za"
AND `EMail` NOT LIKE "%.vn"
AND `EMail` NOT LIKE "%.es"
AND `EMail` NOT LIKE "%.in"
AND `EMail` NOT LIKE "%.dk"
AND `EMail` NOT LIKE "%.ni"
AND `EMail` NOT LIKE "%.ar"
원하는 모든 확장을 넣으십시오.
참고 URL : https://stackoverflow.com/questions/801166/sql-script-to-find-invalid-email-addresses
반응형
'Nice programing' 카테고리의 다른 글
| 오류 1053 : 서비스가 시작 또는 제어 요청에 적시에 응답하지 않았습니다. (0) | 2020.12.06 |
|---|---|
| Response.Cookies보다 Request.Cookies를 언제 사용합니까? (0) | 2020.12.06 |
| DateRange 객체를 만들어야합니까? (0) | 2020.12.05 |
| install.packages를 사용하여 R-forge 패키지를 설치할 수 없습니다. (0) | 2020.12.05 |
| shade plugin에 의해 생성 된 dependency-reduced-pom.xml의 목적은 무엇입니까? (0) | 2020.12.05 |