使用 T-SQL 检查电子邮件地址的有效性

问题

在即时通知的世界中，拥有有效的电子邮件地址非常重要。从组织的角度来看，这使公司可以营销产品并与客户和潜在客户保持联系。对于组织而言，拥有其客户的正确电子邮件地址已经变得至关重要。许多CRM工具（例如Salesforce）都将电子邮件作为数据类型，这允许非常严格的规则来确定电子邮件是否有效并且不会存储无效的电子邮件。在TSQL中，我们没有电子邮件数据类型，那么如何检查电子邮件格式是否有效？

解

在本文中，我将通过一个SQL Server函数来验证电子邮件地址的格式。此功能将考虑确定有效电子邮件的不同规则。

电子邮件语法

 

简而言之，我们可以定义一个有效的电子邮件格式，如下所示：

• 本地部分（电子邮件的核心部分，例如name.lastname或info或support）
• @（以符号分隔）
• 域名（例如mssqltips或gmail）
• 域扩展名（如.com，.net，.org）

它应遵循以下规则：

• 电子邮件需要具有上述所有部分才能生效。因此，类似“ _%@_%._%”的内容。
• 电子邮件中不能包含空格。
• 它不能包含一些特殊字符，例如＃，％。某些域不允许使用。
• 域名或子域名可以包含-和等特殊字符。只要。
• 电子邮件的第一个和最后一个字符不能包含某些特殊字符。
• 电子邮件中的@符号不能超过一个。

TSQL函数来验证电子邮件地址

这是一个函数，返回1表示有效的电子邮件地址格式，返回0表示无效的地址格式。

 

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

让我们创建一个表，插入正确的和不正确的随机电子邮件地址，然后执行功能以验证电子邮件地址。

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

最后，让我们测试一下我们的电子邮件功能。

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

我们可以在下面看到结果。如果为1，则为有效格式，而0为无效格式。