Valid Email Address Check with TSQL

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.

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

Resources

Community

Subscribe