MS SQL中替换邮箱最后一个点后的全部内容

现有数据表 #

id  MyText
1   aa.dd@mail.com
2   aa@mail.com

当前错误的SQL实现 #

用户尝试的代码会匹配字符串中的第一个.就截断替换，无法满足需求：

declare @MyTable table ([id] int primary key clustered, MyText varchar(100))
insert into @MyTable ([id], MyText)
select 1, 'aa.dd@mail.com'
union all select 2, 'aa@mail.com' 

UPDATE @MyTable
SET MyText = LEFT(MyText, CHARINDEX('.', MyText) - 1) + '.test'
WHERE CHARINDEX('.', MyText) > 0

需求说明 #

需要将邮箱字段中最后一个点之后的所有内容替换为"test"，示例如下：

• 'aa.dd@gmail.com' → aa.dd@mail.test
• 'aa@mail.com' → aa@mail.test
• 'aa@mail.maill.mailll.com' → aa@mail.maill.mailll.test

MS SQL正确实现方案 #

MS SQL没有MySQL中SUBSTRING_INDEX直接反向截取的函数，但可以通过REVERSE配合CHARINDEX实现需求，核心思路是反转字符串后定位最后一个.的位置：

declare @MyTable table ([id] int primary key clustered, MyText varchar(100))
insert into @MyTable ([id], MyText)
select 1, 'aa.dd@mail.com'
union all select 2, 'aa@mail.com'
union all select 3, 'aa@mail.maill.mailll.com'

UPDATE @MyTable
SET MyText = LEFT(MyText, LEN(MyText) - CHARINDEX('.', REVERSE(MyText))) + '.test'
WHERE CHARINDEX('.', MyText) > 0

-- 查看更新结果
SELECT * FROM @MyTable

代码解释 #

1. REVERSE(MyText)：反转邮箱字符串，例如aa.dd@mail.com会变成moc.liam@dd.aa
2. CHARINDEX('.', REVERSE(MyText))：找到反转后第一个.的位置，对应原字符串中最后一个.到末尾的字符数+1
3. LEN(MyText) - CHARINDEX('.', REVERSE(MyText))：计算原字符串中最后一个.之前的字符总长度
4. 用LEFT截取到该位置，再拼接.test完成替换

内容的提问来源于stack exchange，提问作者Никита Телегин