복붙노트

[SQL] SQL 스크립트는 잘못된 이메일 주소를 찾을 수

SQL

SQL 스크립트는 잘못된 이메일 주소를 찾을 수

데이터 가져 오기 액세스 데이터베이스에서 수행되고 이메일 주소 필드에 아무런 검증도 없었다. 누군가가 잘못된 이메일 주소의 목록을 반환 할 수있는 SQL 스크립트가 있습니까 (실종 @ 등).

해결법

  1. ==============================

    1.

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

    더 복잡한 아무것도 가능성이 잘못된 부정을 반환하고 느리게 실행됩니다.

    코드에서 전자 메일 주소를 검증하는 것은 사실상 불가능하다.

    편집 : 관련 질문

  2. ==============================

    2.여기에 빠르고 쉬운 솔루션입니다 :

    여기에 빠르고 쉬운 솔루션입니다 :

    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 '_%@__%.__%'

    출처

  3. ==============================

    3.나는 유효한 전자 메일 주소를 반환하는 간단한 T-SQL 쿼리 유용을 찾을 수

    나는 유효한 전자 메일 주소를 반환하는 간단한 T-SQL 쿼리 유용을 찾을 수

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

    PATINDEX 비트는 허용 A-Z에없는 문자, 0-9를 포함하는 모든 전자 메일 주소를 제거 '@', '_'및 '.' '-'문자의 집합입니다.

    당신이 이런 식으로 원하는 일을 되돌릴 수 있습니다 :

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

    4.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})?'
  5. ==============================

    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

    포스트 그레스 버전 :

    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
;
  6. ==============================

    6.SQL 서버 2016까지에

    SQL 서버 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
  7. ==============================

    7.나는이 방법이 더 직관적 찾을 수 있습니다 :

    나는이 방법이 더 직관적 찾을 수 있습니다 :

    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)

    그럼 그냥 함수를 호출 할 필요없이 사용할 수 있습니다.

  8. ==============================

    8.내 기능을 제안한다 :

    내 기능을 제안한다 :

    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
  9. ==============================

    9.

    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/ :이 링크를 확인하시기 바랍니다

  10. ==============================

    10.

    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;

    이것은 나를 위해 잘 작동합니다.

  11. ==============================

    11.

    SELECT Email FROM Employee WHERE NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);
  12. ==============================

    12.

    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)
  13. ==============================

    13.

    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
  14. ==============================

    14.나는 게시물 오래 알고 있지만 3 개월 시간이 지나면 다양한 이메일 조합으로 나는 이메일 ID를 확인하는이 SQL을 만들 수, 가로 질러왔다.

    나는 게시물 오래 알고 있지만 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

    모든 제안을 환영합니다!

  15. ==============================

    15.

    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"

    당신이 원하는 모든 확장을 넣어

    16. from https://stackoverflow.com/questions/801166/sql-script-to-find-invalid-email-addresses by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] PostgreSQL의 : psql의 명령 행 유틸리티를 사용하는 경우 Windows에서 문제를 인코딩  (0) 2020.05.08
[SQL] 파일 시스템 대 데이터베이스의 이미지  (0) 2020.05.08
[SQL] 조인에서 여러 행에 조건을 적용하기위한 SQL  (0) 2020.05.08
[SQL] 싱글 부모 엔터티와 코어 데이터 성능  (0) 2020.05.08
[SQL] MySQL의에서 보관 IPv6 주소  (0) 2020.05.08
,

티스토리툴바