SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create the Contacts table
CREATE TABLE Contacts (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);

-- Insert valid email addresses
INSERT INTO Contacts (ID, Name, Email)
VALUES
    (1, 'John Doe', 'john@example.com'),
    (2, 'Jane Smith', 'jane.smith@example.com'),
    (3, 'Alice Johnson', 'alice123@example.net'),
    (4, 'Bob Brown', 'bob.brown@example.org'),
    (5, 'Emily White', 'emily.white@example.co.uk'),
    (6, 'Michael Lee', 'michael_lee@example.info'),
    (7, 'Sarah Davis', 'sarah-davis@example.us'),
    (8, 'David Wilson', 'david.wilson@example.biz'),
    (9, 'Olivia Taylor', 'olivia.taylor@example.museum'),
    (10, 'James Clark', 'james.clark@example.pro');

-- Insert invalid email addresses
INSERT INTO Contacts (ID, Name, Email)
VALUES
    (11, 'Invalid 1', ''),
    (12, 'Invalid 2', NULL),
    (13, 'Invalid 3', 'john@example.c'),
    (14, 'Invalid 4', 'test[at]example.com'),
    (15, 'Invalid 5', 'missing@domain'),
    (16, 'Invalid 6', 'name@.com'),
    (17, 'Invalid 7', 'name@domain.'),
    (18, 'Invalid 8', 'name@domain..com'),
    (19, 'Invalid 9', 'name@domain_com'),
    (20, 'Invalid 10', 'name@domain.c_o_m');

-- Insert more invalid email addresses with special characters
INSERT INTO Contacts (ID, Name, Email)
VALUES
    (21, 'Invalid 11', 'name@[example].com'),
    (22, 'Invalid 12', 'name@(example).com'),
    (23, 'Invalid 13', 'name@{example}.com'),
    (24, 'Invalid 14', 'name@example[dot]com'),
    (25, 'Invalid 15', 'name@example(dot)com'),
    (26, 'Invalid 16', 'name@example<.com'),
    (27, 'Invalid 17', 'name@example>.com'),
    (28, 'Invalid 18', 'name@example:.com'),
    (29, 'Invalid 19', 'name@example;.com'),
    (30, 'Invalid 20', 'name@example,.com'),
    (31, 'Invalid 21', 'name space@example.com'),
    (32, 'Invalid 22', 'name@@example.com');

-- Display the contents of the Contacts table
SELECT * FROM Contacts;


ID
Name
Email


1
John Doe
john@example.com

2
Jane Smith
jane.smith@example.com

3
Alice Johnson
alice123@example.net

4
Bob Brown
bob.brown@example.org

5
Emily White
emily.white@example.co.uk

6
Michael Lee
michael_lee@example.info

7
Sarah Davis
sarah-davis@example.us

8
David Wilson
david.wilson@example.biz

9
Olivia Taylor
olivia.taylor@example.museum

10
James Clark
james.clark@example.pro

11
Invalid 1


12
Invalid 2
null

13
Invalid 3
john@example.c

14
Invalid 4
test[at]example.com

15
Invalid 5
missing@domain

16
Invalid 6
name@.com

17
Invalid 7
name@domain.

18
Invalid 8
name@domain..com

19
Invalid 9
name@domain_com

20
Invalid 10
name@domain.c_o_m

21
Invalid 11
name@[example].com

22
Invalid 12
name@(example).com

23
Invalid 13
name@{example}.com

24
Invalid 14
name@example[dot]com

25
Invalid 15
name@example(dot)com

26
Invalid 16
name@example<.com

27
Invalid 17
name@example>.com

28
Invalid 18
name@example:.com

29
Invalid 19
name@example;.com

30
Invalid 20
name@example,.com

31
Invalid 21
name space@example.com

32
Invalid 22
name@@example.com


SELECT *
	,CASE 
		WHEN isnull(Email, '') = ''
			THEN 'No'
		WHEN Email LIKE '% %'
			THEN 'No'
		WHEN Email LIKE ('%["(),:;<>\]%')
			THEN 'No'
		WHEN substring(Email, charindex('@', Email), len(Email)) LIKE ('%[!#$%&*+/=?^`_{|]%')
			THEN 'No'
		WHEN (
				left(Email, 1) LIKE ('[-_.+]')
				OR right(Email, 1) LIKE ('[-_.+]')
				)
			THEN 'No'
		WHEN (
				Email LIKE '%[%'
				OR Email LIKE '%]%'
				)
			THEN 'No'
		WHEN Email LIKE '%@%@%'
			OR Email LIKE '%@%..%'
			THEN 'No'
		WHEN Email NOT LIKE '_%@_%.__%'
			THEN 'No'
		ELSE 'Yes'
		END AS IsValidEmail
FROM Contacts


ID
Name
Email
IsValidEmail


1
John Doe
john@example.com
Yes

2
Jane Smith
jane.smith@example.com
Yes

3
Alice Johnson
alice123@example.net
Yes

4
Bob Brown
bob.brown@example.org
Yes

5
Emily White
emily.white@example.co.uk
Yes

6
Michael Lee
michael_lee@example.info
Yes

7
Sarah Davis
sarah-davis@example.us
Yes

8
David Wilson
david.wilson@example.biz
Yes

9
Olivia Taylor
olivia.taylor@example.museum
Yes

10
James Clark
james.clark@example.pro
Yes

11
Invalid 1

No

12
Invalid 2
null
No

13
Invalid 3
john@example.c
No

14
Invalid 4
test[at]example.com
No

15
Invalid 5
missing@domain
No

16
Invalid 6
name@.com
No

17
Invalid 7
name@domain.
No

18
Invalid 8
name@domain..com
No

19
Invalid 9
name@domain_com
No

20
Invalid 10
name@domain.c_o_m
No

21
Invalid 11
name@[example].com
No

22
Invalid 12
name@(example).com
No

23
Invalid 13
name@{example}.com
No

24
Invalid 14
name@example[dot]com
No

25
Invalid 15
name@example(dot)com
No

26
Invalid 16
name@example<.com
No

27
Invalid 17
name@example>.com
No

28
Invalid 18
name@example:.com
No

29
Invalid 19
name@example;.com
No

30
Invalid 20
name@example,.com
No

31
Invalid 21
name space@example.com
No

32
Invalid 22
name@@example.com
No


SELECT *
	,CASE 
		WHEN isnull(Email, '') = ''
			THEN 'No'
		WHEN Email LIKE '% %'
			THEN 'No'
		WHEN PATINDEX('%["(),:;<>]%', Email) <> 0
			THEN 'No'
		WHEN substring(Email, charindex('@', Email), len(Email)) LIKE ('%[!#$%&*+/=?^`_{|]%')
			THEN 'No'
		WHEN (
				left(Email, 1) LIKE ('[-_.+]')
				OR right(Email, 1) LIKE ('[-_.+]')
				)
			THEN 'No'
		WHEN (
				Email LIKE '%[%'
				OR Email LIKE '%]%'
				)
			THEN 'No'
		WHEN Email LIKE '%@%@%'
			OR Email LIKE '%@%..%'
			THEN 'No'
		WHEN PATINDEX('_%@_%.__%', Email) = 0
			THEN 'No'  
		ELSE 'Yes'
		END AS IsValidEmail
FROM Contacts


ID
Name
Email
IsValidEmail


1
John Doe
john@example.com
Yes

2
Jane Smith
jane.smith@example.com
Yes

3
Alice Johnson
alice123@example.net
Yes

4
Bob Brown
bob.brown@example.org
Yes

5
Emily White
emily.white@example.co.uk
Yes

6
Michael Lee
michael_lee@example.info
Yes

7
Sarah Davis
sarah-davis@example.us
Yes

8
David Wilson
david.wilson@example.biz
Yes

9
Olivia Taylor
olivia.taylor@example.museum
Yes

10
James Clark
james.clark@example.pro
Yes

11
Invalid 1

No

12
Invalid 2
null
No

13
Invalid 3
john@example.c
No

14
Invalid 4
test[at]example.com
No

15
Invalid 5
missing@domain
No

16
Invalid 6
name@.com
No

17
Invalid 7
name@domain.
No

18
Invalid 8
name@domain..com
No

19
Invalid 9
name@domain_com
No

20
Invalid 10
name@domain.c_o_m
No

21
Invalid 11
name@[example].com
No

22
Invalid 12
name@(example).com
No

23
Invalid 13
name@{example}.com
No

24
Invalid 14
name@example[dot]com
No

25
Invalid 15
name@example(dot)com
No

26
Invalid 16
name@example<.com
No

27
Invalid 17
name@example>.com
No

28
Invalid 18
name@example:.com
No

29
Invalid 19
name@example;.com
No

30
Invalid 20
name@example,.com
No

31
Invalid 21
name space@example.com
No

32
Invalid 22
name@@example.com
No


CREATE FUNCTION ValidateEmail (@Email VARCHAR(100))
RETURNS VARCHAR(3)
AS
BEGIN
    RETURN (
        SELECT CASE
            WHEN ISNULL(@Email, '') = '' THEN 'No'
            WHEN @Email LIKE '% %' THEN 'No'
            WHEN PATINDEX('%["(),:;<>]%', @Email) <> 0 THEN 'No'
            WHEN SUBSTRING(@Email, CHARINDEX('@', @Email), LEN(@Email)) LIKE ('%[!#$%&*+/=?^`_{|]%') THEN 'No'
            WHEN (LEFT(@Email, 1) LIKE ('[-_.+]') OR RIGHT(@Email, 1) LIKE ('[-_.+]')) THEN 'No'
            WHEN (@Email LIKE '%[%' OR @Email LIKE '%]%') THEN 'No'
            WHEN @Email LIKE '%@%@%' OR @Email LIKE '%@%..%' THEN 'No'
            WHEN PATINDEX('_%@_%.__%', @Email) = 0 THEN 'No'
            ELSE 'Yes'
        END
    )
END;
SELECT *
	, dbo.ValidateEmail(Email) AS IsValidEmail
FROM Contacts


ID
Name
Email
IsValidEmail


1
John Doe
john@example.com
Yes

2
Jane Smith
jane.smith@example.com
Yes

3
Alice Johnson
alice123@example.net
Yes

4
Bob Brown
bob.brown@example.org
Yes

5
Emily White
emily.white@example.co.uk
Yes

6
Michael Lee
michael_lee@example.info
Yes

7
Sarah Davis
sarah-davis@example.us
Yes

8
David Wilson
david.wilson@example.biz
Yes

9
Olivia Taylor
olivia.taylor@example.museum
Yes

10
James Clark
james.clark@example.pro
Yes

11
Invalid 1

No

12
Invalid 2
null
No

13
Invalid 3
john@example.c
No

14
Invalid 4
test[at]example.com
No

15
Invalid 5
missing@domain
No

16
Invalid 6
name@.com
No

17
Invalid 7
name@domain.
No

18
Invalid 8
name@domain..com
No

19
Invalid 9
name@domain_com
No

20
Invalid 10
name@domain.c_o_m
No

21
Invalid 11
name@[example].com
No

22
Invalid 12
name@(example).com
No

23
Invalid 13
name@{example}.com
No

24
Invalid 14
name@example[dot]com
No

25
Invalid 15
name@example(dot)com
No

26
Invalid 16
name@example<.com
No

27
Invalid 17
name@example>.com
No

28
Invalid 18
name@example:.com
No

29
Invalid 19
name@example;.com
No

30
Invalid 20
name@example,.com
No

31
Invalid 21
name space@example.com
No

32
Invalid 22
name@@example.com
No

ID	Name	Email
1	John Doe	john@example.com
2	Jane Smith	jane.smith@example.com
3	Alice Johnson	alice123@example.net
4	Bob Brown	bob.brown@example.org
5	Emily White	emily.white@example.co.uk
6	Michael Lee	michael_lee@example.info
7	Sarah Davis	sarah-davis@example.us
8	David Wilson	david.wilson@example.biz
9	Olivia Taylor	olivia.taylor@example.museum
10	James Clark	james.clark@example.pro
11	Invalid 1
12	Invalid 2	null
13	Invalid 3	john@example.c
14	Invalid 4	test[at]example.com
15	Invalid 5	missing@domain
16	Invalid 6	name@.com
17	Invalid 7	name@domain.
18	Invalid 8	name@domain..com
19	Invalid 9	name@domain_com
20	Invalid 10	name@domain.c_o_m
21	Invalid 11	name@[example].com
22	Invalid 12	name@(example).com
23	Invalid 13	name@{example}.com
24	Invalid 14	name@example[dot]com
25	Invalid 15	name@example(dot)com
26	Invalid 16	name@example<.com
27	Invalid 17	name@example>.com
28	Invalid 18	name@example:.com
29	Invalid 19	name@example;.com
30	Invalid 20	name@example,.com
31	Invalid 21	name space@example.com
32	Invalid 22	name@@example.com