SQL:根据第二个表替换多行上的部分列 [英] SQL: Replace part of a column on multiple rows based on second table

问题描述

我有一个表 (pages) 保存网页的属性，包括页面内容本身的列，NVARCHAR(MAX) 列.

I have a table (pages) that holds properties for web pages, including a column for the page content itself, an NVARCHAR(MAX) column.

在该列中，我需要查找并替换一堆文本字符串并将它们替换为其他文本字符串；这些相关性位于第二个表 (moving) 中，带有 oldValue 和 newValue 列.

Within that column, I need to find and replace a bunch of text strings and replace them with other text strings; these correlations are in a second table (moving), with an oldValue and newValue column.

例如，如果我从这样的两个表开始:

So, for example, if I'm starting with the two tables like this:

页面表:

ID Content
1  Words words Ancient words
2  Blah blah OutWithTheOld blah
3  Etc etc Useless etc

移动桌子:

OldValue          NewValue
Ancient           Better
OutWithTheOld     InWithTheNew
Useless           Useful

...我需要一种方法来制作一个像这样离开页面表的替换:

...I need a way of making a Replace that leaves the pages table like this:

ID Content
1  Words words Better words
2  Blah blah InWithTheNew blah
3  Etc etc Useful etc

pages 表中的给定记录可能需要多次替换，并且无法预测 pages 记录是否有一个、一个或多个必要的替换，或者来自 moving.oldvalue 的哪些值 会被发现，需要更换.

It is possible that a given record in the pages table will need multiple replacements, and there's no way of predicting whether a pages record will have none, one, or many necessary replacements, or which values from moving.oldvalue will be found and need to be replaced.

我使用的是 SQL Server 2008，而且我对它很陌生.非常感谢您提供的任何帮助！

I'm using SQL Server 2008, and I'm fairly new to it. Thanks so much in advance for any help you can give!

推荐答案

这是一个使用 CTE 的单语句非光标方法:

Here's a single-statement, non-cursor method which makes use of a CTE:

WITH CTE(iteration, page_id, content) AS (
    SELECT
        0,
        P.page_id,
        REPLACE(P.content, M1.old_value, M1.new_value)
    FROM
        Pages P
    INNER JOIN Moving M1 ON
        P.content LIKE '%' + M1.old_value + '%'
    WHERE
        NOT EXISTS (SELECT * FROM Moving M2 WHERE P.content LIKE '%' + M2.old_value + '%' AND M2.moving_id < M1.moving_id)
    UNION ALL
    SELECT
        CTE.iteration + 1,
        CTE.page_id,
        REPLACE(CTE.content, M3.old_value, M3.new_value)
    FROM
        CTE
    INNER JOIN Moving M3 ON
        CTE.content LIKE '%' + M3.old_value + '%'
    WHERE
        NOT EXISTS (SELECT * FROM Moving M4 WHERE CTE.content LIKE '%' + M4.old_value + '%' AND M4.moving_id < M3.moving_id)
)
UPDATE P2
SET
    content = CTE1.content
FROM
    Pages P2
INNER JOIN CTE CTE1 ON
    CTE1.page_id = P2.page_id AND
    NOT EXISTS (SELECT * FROM CTE CTE2 WHERE page_id = P2.page_id AND CTE2.iteration > CTE1.iteration)

这篇关于SQL:根据第二个表替换多行上的部分列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！