Skip to main content

SQL Server T-SQL: Implement Custom Email Validation Rules with the LIKE Clause and the PATINDEX function

In this post, we will demonstrate how to check and validate email addresses in T-SQL using the LIKE clause and the built-in PATINDEX function. By catching and addressing email validation issues at the database level, we can reduce the chances of errors propagating through downstream processes. In addition, sending emails to invalid addresses can harm your sender reputation and lead to your emails being flagged as spam. Let's get started validating email addresses with T-SQL to help maintain a positive sender reputation.

Valid and Invalid Email Samples​

You can use this Fiddle to follow along and practice email validation with T-SQL

Create Contacts table with valid and invalid emails for testing
-- 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;

Contacts Table

IDNameEmail
1John Doejohn@example.com
2Jane Smithjane.smith@example.com
3Alice Johnsonalice123@example.net
4Bob Brownbob.brown@example.org
5Emily Whiteemily.white@example.co.uk
6Michael Leemichael_lee@example.info
7Sarah Davissarah-davis@example.us
8David Wilsondavid.wilson@example.biz
9Olivia Taylorolivia.taylor@example.museum
10James Clarkjames.clark@example.pro
11Invalid 1
12Invalid 2null
13Invalid 3john@example.c
14Invalid 4test[at]example.com
15Invalid 5missing@domain
16Invalid 6name@.com
17Invalid 7name@domain.
18Invalid 8name@domain..com
19Invalid 9name@domain_com
20Invalid 10name@domain.c_o_m
21Invalid 11name@[example].com
22Invalid 12name@(example).com
23Invalid 13name@{example}.com
24Invalid 14name@example[dot]com
25Invalid 15name@example(dot)com
26Invalid 16name@example<.com
27Invalid 17name@example>.com
28Invalid 18name@example:.com
29Invalid 19name@example;.com
30Invalid 20name@example,.com
31Invalid 21name space@example.com
32Invalid 22name@@example.com

Email Validation Rules with the LIKE Clause​

Use the LIKE clause to check email address pattern
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

The aobve T-SQL query is designed to determine the validity of email addresses stored in the "Contacts" table. It employs a series of conditions to evaluate various aspects of each email address and assigns an "IsValidEmail" status based on the assessment. Let's have a closer look at each condition and its purpose:

  1. isnull(Email, '') = '': This condition checks if the "Email" field is empty (NULL or an empty string). If the email is empty, it's marked as invalid.

  2. Email LIKE '% %': This condition searches for spaces within the email address. If a space is found, the email is considered invalid.

  3. Email LIKE ('%["(),:;<>]%'): This condition checks if the email address contains any of the specified characters: double quotes, parentheses, commas, colons, semicolons, less-than signs, greater-than signs. If any of these characters are found, the email is marked as invalid.

  4. substring(Email, charindex('@', Email), len(Email)) LIKE ('%[!#$%&*+/=?^`_{|]%'): This condition extracts the portion of the email address after the "@" symbol and checks if it contains any of the specified special characters. If such characters are present, the email is considered invalid.

  5. left(Email, 1) LIKE ('[-_.+]') OR right(Email, 1) LIKE ('[-_.+]'): This condition checks if the email address starts or ends with certain special characters: hyphen, underscore, period, or plus sign. If it does, the email is marked as invalid.

  6. Email LIKE '%[%' OR Email LIKE '%]%': This condition checks if the email contains square brackets. If square brackets are found, the email is considered invalid.

  7. Email LIKE '%@%@%' OR Email LIKE '%@%..%': This condition checks for multiple "@" symbols in the email address or for consecutive dots after the "@" symbol. If either of these patterns is detected, the email is marked as invalid.

  8. Email NOT LIKE '_%@_%.__%': This condition checks if the email address follows a basic format of having at least a single character before the "@" symbol, at least one character between the "@" symbol and the dot, and at least two characters after the dot.

The query evaluates each of these conditions sequentially. If any of the conditions are met, the "IsValidEmail" status is set to "No," indicating that the email is invalid. If none of the conditions are met, the email is considered valid and the "IsValidEmail" status is set to "Yes."

Query Result

IDNameEmailIsValidEmail
1John Doejohn@example.comYes
2Jane Smithjane.smith@example.comYes
3Alice Johnsonalice123@example.netYes
4Bob Brownbob.brown@example.orgYes
5Emily Whiteemily.white@example.co.ukYes
6Michael Leemichael_lee@example.infoYes
7Sarah Davissarah-davis@example.usYes
8David Wilsondavid.wilson@example.bizYes
9Olivia Taylorolivia.taylor@example.museumYes
10James Clarkjames.clark@example.proYes
11Invalid 1No
12Invalid 2nullNo
13Invalid 3john@example.cNo
14Invalid 4test[at]example.comNo
15Invalid 5missing@domainNo
16Invalid 6name@.comNo
17Invalid 7name@domain.No
18Invalid 8name@domain..comNo
19Invalid 9name@domain_comNo
20Invalid 10name@domain.c_o_mNo
21Invalid 11name@[example].comNo
22Invalid 12name@(example).comNo
23Invalid 13name@{example}.comNo
24Invalid 14name@example[dot]comNo
25Invalid 15name@example(dot)comNo
26Invalid 16name@example<.comNo
27Invalid 17name@example>.comNo
28Invalid 18name@example:.comNo
29Invalid 19name@example;.comNo
30Invalid 20name@example,.comNo
31Invalid 21name space@example.comNo
32Invalid 22name@@example.comNo

Email Validation Rules with the PATINDEX Function​

Note that you also have the option to use the built-in PATINDEX Function to implement custom email validation rules. I have replaced 2 LIKE clauses using PATINDEX function to demonstrate how this can be done. You will get the same email validation results.

Use PATINDEX Function to check email address pattern
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

Query Result

IDNameEmailIsValidEmail
1John Doejohn@example.comYes
2Jane Smithjane.smith@example.comYes
3Alice Johnsonalice123@example.netYes
4Bob Brownbob.brown@example.orgYes
5Emily Whiteemily.white@example.co.ukYes
6Michael Leemichael_lee@example.infoYes
7Sarah Davissarah-davis@example.usYes
8David Wilsondavid.wilson@example.bizYes
9Olivia Taylorolivia.taylor@example.museumYes
10James Clarkjames.clark@example.proYes
11Invalid 1No
12Invalid 2nullNo
13Invalid 3john@example.cNo
14Invalid 4test[at]example.comNo
15Invalid 5missing@domainNo
16Invalid 6name@.comNo
17Invalid 7name@domain.No
18Invalid 8name@domain..comNo
19Invalid 9name@domain_comNo
20Invalid 10name@domain.c_o_mNo
21Invalid 11name@[example].comNo
22Invalid 12name@(example).comNo
23Invalid 13name@{example}.comNo
24Invalid 14name@example[dot]comNo
25Invalid 15name@example(dot)comNo
26Invalid 16name@example<.comNo
27Invalid 17name@example>.comNo
28Invalid 18name@example:.comNo
29Invalid 19name@example;.comNo
30Invalid 20name@example,.comNo
31Invalid 21name space@example.comNo
32Invalid 22name@@example.comNo

Encapsulate Email Validation Logic in a Scalar Function​

To re-use your email validation logic in different SQL queries or reports, you can create a scalar-valued function in T-SQL that takes an email address as input and returns whether the email address is valid or not based on the conditions you've provided. Here's how you can define the function:

Custom Email Validation Function
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;

You can invoke the function to validate the email in each Contact record.

Using ValidateEmail function in the SELECT query
SELECT *
	, dbo.ValidateEmail(Email) AS IsValidEmail
FROM Contacts

Conclusion​

It is important to note that while basic validation in T-SQL can catch some errors at database level, it won't cover all possible edge cases. More comprehensive email validation should ideally involve using dedicated email validation libraries, regular expressions, or external services that are designed to handle the complexities of email address validation.