[DATABASE/SQLSERVER] E 메일 주소 검증하기

icodebroker SQLSERVER 2024-06-18

■ E 메일 주소를 검증하는 방법을 보여준다.

▶ E 메일 주소 검증하기 예제 (SQL)


SELECT dbo.ValidateEMailAddress('icodebroker@naver.com');

1

2

3

SELECT dbo.ValidateEMailAddress('icodebroker@naver.com');

▶ E 메일 주소 검증하기 (SQL)


CREATE FUNCTION [dbo].[ValidateEMailAddress]
(
    @EMailAddress VARCHAR(255)
)
RETURNS BIT
AS
BEGIN
    RETURN
    (
        SELECT
            CASE
                WHEN @EMailAddress IS NULL                                        THEN 0
                WHEN CHARINDEX(' ', @EMailAddress) <> 0 OR
                     CHARINDEX('/', @EMailAddress) <> 0 OR
                     CHARINDEX(':', @EMailAddress) <> 0 OR
                     CHARINDEX(';', @EMailAddress) <> 0                           THEN 0
                WHEN LEN(@EMailAddress) - 1 <= CHARINDEX('.', @EMailAddress)      THEN 0
                WHEN @EMailAddress LIKE '%@%@%' OR @EMailAddress NOT LIKE '%@%.%' THEN 0
                ELSE                                                                   1
	      END
    );
END
GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

CREATE FUNCTION [dbo].[ValidateEMailAddress]

(

@EMailAddress VARCHAR(255)

)

RETURNS BIT

AS

BEGIN

RETURN

(

SELECT

CASE

WHEN @EMailAddress IS NULL THEN 0

WHEN CHARINDEX(' ', @EMailAddress) <> 0 OR

CHARINDEX('/', @EMailAddress) <> 0 OR

CHARINDEX(':', @EMailAddress) <> 0 OR

CHARINDEX(';', @EMailAddress) <> 0 THEN 0

WHEN LEN(@EMailAddress) - 1 <= CHARINDEX('.', @EMailAddress) THEN 0

WHEN @EMailAddress LIKE '%@%@%' OR @EMailAddress NOT LIKE '%@%.%' THEN 0

ELSE 1

END

);

END

GO