Sql: Replace Part Of A Column On Multiple Rows Based On Second Table

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

Solution 1:

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

WITH CTE(iteration, page_id, content) AS (
    SELECT0,
        P.page_id,
        REPLACE(P.content, M1.old_value, M1.new_value)
    FROM
        Pages P
    INNERJOIN Moving M1 ON
        P.content LIKE'%'+ M1.old_value +'%'WHERENOTEXISTS (SELECT*FROM Moving M2 WHERE P.content LIKE'%'+ M2.old_value +'%'AND M2.moving_id < M1.moving_id)
    UNIONALLSELECT
        CTE.iteration +1,
        CTE.page_id,
        REPLACE(CTE.content, M3.old_value, M3.new_value)
    FROM
        CTE
    INNERJOIN Moving M3 ON
        CTE.content LIKE'%'+ M3.old_value +'%'WHERENOTEXISTS (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
INNERJOIN CTE CTE1 ON
    CTE1.page_id = P2.page_id ANDNOTEXISTS (SELECT*FROM CTE CTE2 WHERE page_id = P2.page_id AND CTE2.iteration > CTE1.iteration)

Solution 2:

Try this

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

DECLARE db_cursor CURSORFORSELECT*FROM Moving

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

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

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