SQL中如何实现通用的邮箱地址后缀提取逻辑

需求规则 #

需要从邮箱地址中提取指定后缀，规则如下：

• 输入test@work.com时，输出com
• 输入test@work.test.com时，输出test.com

原有方案问题 #

之前使用的反转截取逻辑，本质是取字符串最后一个.后的内容，仅能匹配单级后缀的场景，遇到多级后缀的邮箱就会返回错误结果。

正确实现逻辑 #

核心逻辑分三步：

1. 定位@符号位置，剥离邮箱前缀，拿到@后的完整域名
2. 在域名部分定位第一个.的位置
3. 截取该.后的所有内容，即为目标后缀

基础版本代码（SQL Server语法） #

SELECT 
  RIGHT(
    [你的邮箱字段名],
    LEN([你的邮箱字段名]) - CHARINDEX('.', STUFF([你的邮箱字段名], 1, CHARINDEX('@', [你的邮箱字段名]), '')) - CHARINDEX('@', [你的邮箱字段名])
  ) AS email_suffix
FROM [你的表名]

可以直接用常量测试效果：

-- 测试用例1 输出com
SELECT RIGHT('test@work.com', LEN('test@work.com') - CHARINDEX('.', STUFF('test@work.com', 1, CHARINDEX('@', 'test@work.com'), '')) - CHARINDEX('@', 'test@work.com'))
-- 测试用例2 输出test.com
SELECT RIGHT('test@work.test.com', LEN('test@work.test.com') - CHARINDEX('.', STUFF('test@work.test.com', 1, CHARINDEX('@', 'test@work.test.com'), '')) - CHARINDEX('@', 'test@work.test.com'))

容错版本代码 #

如果表中存在非法格式的邮箱数据（缺少@或域名部分无.），可以加判断避免报错：

SELECT 
  CASE
    WHEN CHARINDEX('@', [你的邮箱字段名]) > 0 
      AND CHARINDEX('.', STUFF([你的邮箱字段名], 1, CHARINDEX('@', [你的邮箱字段名]), '')) > 0
    THEN RIGHT(
      [你的邮箱字段名],
      LEN([你的邮箱字段名]) - CHARINDEX('.', STUFF([你的邮箱字段名], 1, CHARINDEX('@', [你的邮箱字段名]), '')) - CHARINDEX('@', [你的邮箱字段名])
    )
    ELSE NULL
  END AS email_suffix
FROM [你的表名]

内容的提问来源于stack exchange，提问作者A Coder