如何在SQL中替换URL的newsId值并双向更新News表记录

表结构与示例数据 #

现有News表，包含ID、NewsRoleId、NewsTitle、URL字段，每条URL格式如http://ournews.com/View-News;NewsId=56122;OrderId=1;pt=5。每个NewsTitle对应两条不同NewsRoleId的记录，示例数据：

ID         NewsTitle       NewsRoleId       URL
1             Test            124           http://ournews.com/View;newsId=44;OrderId=1;pt=5
2             Test            138           http://ournews.com/View;newsId=32;OrderId=1;pt=5

需求说明 #

需要双向替换两条记录URL中的newsId值：

• 将NewsRoleId=124记录的ID替换到NewsRoleId=138记录的URL的newsId位置
• 将NewsRoleId=138记录的ID替换到NewsRoleId=124记录的URL的newsId位置

期望更新后的数据：

ID         NewsTitle       NewsRoleId       URL
1           Test              124          http://ournews.com/View;newsId=2;OrderId=1;pt=5
2           Test              138          http://ournews.com/View;newsId=1;OrderId=1;pt=5

当前问题 #

我编写的更新语句无法准确定位newsId=随机数值的部分，语句如下：

Update News
SET URL= REPLACE(Url, 'newsID=123433', 'newsId='+CAST(Select Id from News where NewsTitle= 'test' and NewsRoleID= 124) as varchar)
where NewsRoleID = 138 and NewsTitle = 'test'

解决方案 #

思路 #

由于newsId后的数值是动态的，不能用固定字符串匹配，需要通过字符串定位找到newsId=的起始位置，再找到后续的分隔符（;）确定需要替换的数字范围，最终替换为目标ID。

完整SQL语句（以SQL Server为例） #

通过自连接获取对应记录的ID，再用STUFF函数替换URL中的newsId数值：

-- 更新NewsRoleId=138的记录，替换为同标题下NewsRoleId=124记录的ID
UPDATE n1
SET n1.URL = STUFF(n1.URL, 
                   CHARINDEX('newsId=', n1.URL) + 7, 
                   CHARINDEX(';', n1.URL, CHARINDEX('newsId=', n1.URL)) - (CHARINDEX('newsId=', n1.URL) + 7),
                   CAST(n2.ID AS VARCHAR(10))
                  )
FROM News n1
JOIN News n2 ON n1.NewsTitle = n2.NewsTitle 
            AND n1.NewsRoleId = 138 
            AND n2.NewsRoleId = 124

-- 更新NewsRoleId=124的记录，替换为同标题下NewsRoleId=138记录的ID
UPDATE n1
SET n1.URL = STUFF(n1.URL, 
                   CHARINDEX('newsId=', n1.URL) + 7, 
                   CHARINDEX(';', n1.URL, CHARINDEX('newsId=', n1.URL)) - (CHARINDEX('newsId=', n1.URL) + 7),
                   CAST(n2.ID AS VARCHAR(10))
                  )
FROM News n1
JOIN News n2 ON n1.NewsTitle = n2.NewsTitle 
            AND n1.NewsRoleId = 124 
            AND n2.NewsRoleId = 138

语句解释 #

1. 自连接关联：通过NewsTitle关联同标题下的两条不同NewsRoleId的记录，获取需要替换的目标ID。
2. CHARINDEX定位：
   • CHARINDEX('newsId=', n1.URL) + 7：找到newsId=的起始位置后，偏移7位（newsId=的长度），得到需要替换的数字起始位置。
   • CHARINDEX(';', n1.URL, CHARINDEX('newsId=', n1.URL))：找到newsId=之后第一个;的位置，以此确定需要替换的数字长度。
3. STUFF函数：在URL的指定位置替换掉原有的数字，替换为关联记录的ID。

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