SQL: replace part of a column with multiple rows based on the second table

Question

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

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

So, for example, if I start with two tables as follows:

page 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 to make a replacement that leaves the page table as follows:

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

It is possible that for this record in the page table several substitutions will be required, and there is no way to predict whether the page record will not have any, one or several necessary replacements, or what values ​​from moving.oldvalue will be found and need to be replaced.

I am using SQL Server 2008 and I am pretty new to this. Thanks so much for any help you can give!

Answer 1

This uses a single-task, non-cursor method that uses 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) 

Answer 2

try it

 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 don't like cursors at all, this should do the trick for you