Valid Email Address Check with TSQL

By:   |   Updated: 2020-08-18   |   Comments (3)   |   Related: More > Functions User Defined UDF

Free SQL Server Performance and Monitoring Report

Dear Database Professional,

Download your free copy of the SQL Server Performance and Monitoring Report. This survey was conducted in 2022 and polled 588 database professionals about various aspects of tuning and optimizing SQL Server.

Click here to download the free report


In this world of instant notification, having a valid email address is very important. From an organizational perspective, this allows companies to market their products and stay in touch with customers and prospective customers. It has become critical for an organization to have the correct email address of their customer. Many CRM tools, like Salesforce, have email as a data type which allows for very stringent rules determining if an email is valid and won’t store an invalid email. In TSQL, we donít have an email data type, so how can we check that an email format is valid?


In this article, I will go through a SQL Server function to validate the format of an email address. This function will take into account the different rules of determining a valid email.

Email Syntax

If you want to dig into the email standards, you can look at RFC 8398.

In a nutshell, we can define a valid email format like the following:

  • local part (core part of the email like name.lastname or info or support)
  • @ (at sign as a separator)
  • domain name (like mssqltips or gmail)
  • domain extension (like .com, .net, .org)

It should follow these rules:

  • An email needs to have all the above parts to make it valid. Therefore something like this '_%@_%._%'.
  • The email cannot contain spaces.
  • It cannot contain some special characters like #, %. It is not allowed by some domains.
  • The domain or subdomain name can contain special characters like - and . only.
  • The first and last character of the email cannot contain some of the special characters.
  • There cannot be more than one @ sign in the email.

TSQL Function to Validate Email Addresses

Here is a function that returns 1 for valid email address format and 0 for invalid address format.

CREATE FUNCTION dbo.ChkValidEmail(@EMAIL varchar(100))RETURNS bit as
  DECLARE @bitEmailVal as Bit
  DECLARE @EmailText varchar(100)

  SET @EmailText=ltrim(rtrim(isnull(@EMAIL,'')))

  SET @bitEmailVal = case when @EmailText = '' then 0
                          when @EmailText like '% %' then 0
                          when @EmailText like ('%["(),:;<>\]%') then 0
                          when substring(@EmailText,charindex('@',@EmailText),len(@EmailText)) like ('%[!#$%&*+/=?^`_{|]%') then 0
                          when (left(@EmailText,1) like ('[-_.+]') or right(@EmailText,1) like ('[-_.+]')) then 0                                                                                    
                          when (@EmailText like '%[%' or @EmailText like '%]%') then 0
                          when @EmailText LIKE '%@%@%' then 0
                          when @EmailText NOT LIKE '_%@_%._%' then 0
                          else 1 
  RETURN @bitEmailVal

Let's create a table, insert random correct and incorrect email address then execute the function to verify the email addresses.

create table dbo.sample_emails (  email varchar(50) )GO

-- Insert email values
insert dbo.sample_emails values
  ('[email protected]'),
  ('[email protected]'),
  ('[email protected]'),
  ('[email protected]'),
  ('[email protected]'),
  ('[email protected]'),
  ('[email protected]'),
  ('[email protected]'),
  ('info [email protected]'),
  ('[email protected]'),
  ('[email protected]'),

Finally, let's test our email function.

select email, dbo.ChkValidEmail(email) as Validity from dbo.sample_emails

We can see the results below.  If it is a 1 it is a valid format and 0 is an invalid format.

email function testing


Different CRM tools can have different email rules. Also different domains have different rules for creating email addresses. This function is just a generic one and can be modified as per the requirement.

Next Steps

get scripts

next tip button

About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips

Article Last Updated: 2020-08-18

Comments For This Article

Tuesday, July 26, 2022 - 7:05:34 AM - Martin Back To Top (90310)
Hi Matteo,

Thank you for your code. I have one small remark. It seems that emailaddresses starting with an underscore are allowed (I have one customer in my database with the address [email protected] )

This address isn't seen as valid emailaddress with this function



Monday, January 25, 2021 - 7:22:07 AM - Radu Back To Top (88097)
Luis Catarino asked:
<i>What if the string has 2 emails separated by ;</i>

Below is the code of Matteo modified to accept this situation:

create function dbo.ChkValidEmail(@EMAIL varchar(100)) returns bit as
-- (c) Matteo Lorini -
-- code taken from there and extended to accept multiple addresses separated by ;
declare @bitEmailVal bit
declare @EmailText varchar(100)
declare @atposition int
set @EMAIL=ltrim(rtrim(isnull(@EMAIL,'')))
if @EMAIL=''
set @bitEmailVal=0
set @bitEmailVal=1
while @bitEmailVal=1 and len(@EMAIL)>0
while charindex(';',@EMAIL)=1 and len(@EMAIL)>1
set @EMAIL=right(@EMAIL,len(@EMAIL)-1)
if charindex(';',@EMAIL)=0
set @[email protected]
set @EMAIL=''
set @EmailText=left(@EMAIL,charindex(';', @EMAIL)-1)
set @EMAIL=right(@EMAIL, len(@EMAIL)-charindex(';',@EMAIL))
set @atposition=charindex('@',@EmailText)
set @bitEmailVal = case
when @atposition=0 then 0
when @EmailText = '' then 0
when @EmailText like '% %' then 0
when @EmailText like ('%["(),:;<>\]%') then 0
when substring(@EmailText,charindex('@',@EmailText),len(@EmailText)) like ('%[!#$%&*+/=?^`_{|]%') then 0
when (left(@EmailText,1) like ('[-_.+]') or right(@EmailText,1) like ('[-_.+]') or left(@EmailText,charindex('@',@EmailText)-1) like ('[-_.+]')) then 0
when (@EmailText like '%[%' or @EmailText like '%]%') then 0
when @EmailText LIKE '%@%@%' then 0
when @EmailText NOT LIKE '_%@_%.__%' then 0
else 1
return @bitEmailVal

Friday, November 27, 2020 - 2:01:13 PM - Luis Catarino Back To Top (87858)
What if the string has 2 emails separated by ;

get free sql tips
agree to terms