Valid Email Address Check with TSQL


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


Problem

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?

Solution

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
BEGIN     
  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 
                      end
  RETURN @bitEmailVal
END 
GO

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
  ('info@mssqltips.com'),
  ('info.support@mssqltips.com'),
  ('.info@mssqltips.com'),
  ('info..@mssqltips.com'),
  ('info@mssqltips.c'),
  ('info@support@mssqltips.com'),
  ('info.support@mssql_tips.com'),
  ('+info@mssqltips.com'),
  ('info Support@mssqltips.com'),
  ('info@mssql tips.com'),
  ('NULL'),
  ('22@mssqltips.com'),
  ('@mssqltips.com')
GO

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

Conclusion

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




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 mssqltips.com - https://www.mssqltips.com/sqlservertip/6519/valid-email-address-check-with-tsql/
-- code taken from there and extended to accept multiple addresses separated by ;
begin
declare @bitEmailVal bit
declare @EmailText varchar(100)
declare @atposition int
set @EMAIL=ltrim(rtrim(isnull(@EMAIL,'')))
if @EMAIL=''
set @bitEmailVal=0
else
set @bitEmailVal=1
while @bitEmailVal=1 and len(@EMAIL)>0
begin
while charindex(';',@EMAIL)=1 and len(@EMAIL)>1
set @EMAIL=right(@EMAIL,len(@EMAIL)-1)
if charindex(';',@EMAIL)=0
begin
set @EmailText=@EMAIL
set @EMAIL=''
end
else
begin
set @EmailText=left(@EMAIL,charindex(';', @EMAIL)-1)
set @EMAIL=right(@EMAIL, len(@EMAIL)-charindex(';',@EMAIL))
end
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
end
end
return @bitEmailVal
end

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


download














get free sql tips
agree to terms