[MSSQL]이메일 유효성 체크 쿼리

-- Before

SELECT COUNT(1) FROM EMS_AUTO_SCHEDULE_INFO A, EMS_AUTO_SEND_LIST_01 B
WHERE A.WORKDAY = B.WORKDAY AND A.SEQNO = B.SEQNO
AND A.WORKDAY = CONVERT(VARCHAR(8),GETDATE(),112)
AND A.SEND_FLAG = '05' AND A.REQ_DATE < GETDATE()
AND DATEADD(MI,+30,B.REG_DATE) <= GETDATE()
AND B.SEND_TIME IS NULL
AND B.TO_EMAIL IS NOT NULL
AND B.TO_EMAIL <> ''

-- After(이메일 형식 X)

SELECT COUNT(1) FROM EMS_AUTO_SCHEDULE_INFO A, EMS_AUTO_SEND_LIST_01 B
WHERE A.WORKDAY = B.WORKDAY AND A.SEQNO = B.SEQNO
AND A.WORKDAY = CONVERT(VARCHAR(8),GETDATE(),112)
AND A.SEND_FLAG = '05' AND A.REQ_DATE < GETDATE()
AND DATEADD(MI,+30,B.REG_DATE) <= GETDATE()
AND B.SEND_TIME IS NULL
AND B.TO_EMAIL IS NOT NULL
AND B.TO_EMAIL <> ''
AND NOT
(CHARINDEX(' ',LTRIM(RTRIM(B.TO_EMAIL))) = 0
AND LEFT(LTRIM(B.TO_EMAIL),1) <> '@'
AND RIGHT(RTRIM(B.TO_EMAIL),1) <> '.'
AND CHARINDEX('.',B.TO_EMAIL,CHARINDEX('@',B.TO_EMAIL)) - CHARINDEX('@',B.TO_EMAIL) > 1
AND LEN(LTRIM(RTRIM(B.TO_EMAIL))) - LEN(REPLACE(LTRIM(RTRIM(B.TO_EMAIL)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(B.TO_EMAIL)))) >= 3
AND    (CHARINDEX('.@',B.TO_EMAIL) = 0 AND CHARINDEX('..',B.TO_EMAIL) = 0)
)

-- After(이메일 형식 O)

SELECT COUNT(1) FROM EMS_AUTO_SCHEDULE_INFO A, EMS_AUTO_SEND_LIST_01 B
WHERE A.WORKDAY = B.WORKDAY AND A.SEQNO = B.SEQNO
AND A.WORKDAY = CONVERT(VARCHAR(8),GETDATE(),112)
AND A.SEND_FLAG = '05' AND A.REQ_DATE < GETDATE()
AND DATEADD(MI,+30,B.REG_DATE) <= GETDATE()
AND B.SEND_TIME IS NULL
AND B.TO_EMAIL IS NOT NULL
AND B.TO_EMAIL <> ''
AND
(CHARINDEX(' ',LTRIM(RTRIM(B.TO_EMAIL))) = 0
AND LEFT(LTRIM(B.TO_EMAIL),1) <> '@'
AND RIGHT(RTRIM(B.TO_EMAIL),1) <> '.'
AND CHARINDEX('.',B.TO_EMAIL,CHARINDEX('@',B.TO_EMAIL)) - CHARINDEX('@',B.TO_EMAIL) > 1
AND LEN(LTRIM(RTRIM(B.TO_EMAIL))) - LEN(REPLACE(LTRIM(RTRIM(B.TO_EMAIL)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(B.TO_EMAIL)))) >= 3
AND    (CHARINDEX('.@',B.TO_EMAIL) = 0 AND CHARINDEX('..',B.TO_EMAIL) = 0)
)