SQL: Replace part of a column on multiple rows based on second table

Question

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

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:

pages table:

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

moving table:

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

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.

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

score 3 · Answer 1 · answered Aug 26 '11 at 17:13

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)

+1, was thinking about a recursive CTE too, but in conjunction with row_number(). — Andriy M, Aug 27 '11 at 00:55

score 2 · Answer 2 · answered Aug 26 '11 at 17:00

Try this

DECLARE @OldV NVARCHAR(32)    -- Adjust for your field sizes in MOVING
DECLARE @NEWV NVARCHAR(32)

DECLARE db_cursor CURSOR FOR  
SELECT *
FROM Moving

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @OldV,@newV

WHILE @@FETCH_STATUS = 0   
BEGIN   
    UPDATE Pages SET content=REPLACE(content,@oldV,@NewV)
      WHERE content LIKE '%'+@OldV+'%'
       FETCH NEXT FROM db_cursor INTO @oldV,@NewV
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Although I generally don't like cursors, this should do the trick for you

Wow! Totally beat me to this one. Besides the variable names, my code looked almost exactly like yours! — Zach, Aug 26 '11 at 17:01

SQL: Replace part of a column on multiple rows based on second table

2 Answers2

Linked