T-SQL如何从含多分隔符的邮箱ID中提取正确邮箱主域名

问题说明 #

• 表字段col_email存储全量邮箱地址，提取规则为：取@符号之后、第一个.符号之前的字符串作为主域名，需兼容任意级数的点分隔后缀场景
• 原有查询逻辑仅适配单级后缀邮箱：处理abc@gmail.com可正确返回gmail，但处理abc@gmail.co.com这类多级后缀邮箱时会错误返回gmail.co，不符合预期

原有错误实现代码：

SUBSTRING(col_email, 
    CHARINDEX('@', col_email) + 1, 
    LEN(col_email) - CHARINDEX('@', col_email) - CHARINDEX('.', REVERSE(col_email))) as domain

问题原因 #

原有逻辑通过倒序查找字符串末尾的第一个.来计算截取长度，遇到多级后缀时会把@和末尾点之间的所有中间段都算入结果，本质是找错了截取的终止位置——我们不需要关注字符串末尾的点位置，只需要定位@符号之后出现的第一个点的位置即可。

修正后实现 #

SUBSTRING(
    col_email,
    -- 截取起始位：@符号的下一位
    CHARINDEX('@', col_email) + 1,
    -- 截取长度：@后第一个点的位置 减去 @的位置 再减1
    CHARINDEX('.', col_email, CHARINDEX('@', col_email) + 1) - CHARINDEX('@', col_email) - 1
) AS domain

效果验证 #

• 输入abc@gmail.com：返回gmail，符合预期
• 输入abc@gmail.co.com：返回gmail，符合预期
• 输入abc@enterprise.mail.co.uk：返回enterprise，不受后面多级后缀影响

注：以上逻辑默认邮箱格式合法，即字符串中存在@且@后存在至少一个点，若业务中存在格式异常的脏数据，可在外层加CASE WHEN做容错判断。

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