sql - How to make a nested replace of values from another table?

Question

I have two tables with the following (dummy) structure:

Table 1
idText     sText(nvarchar(500))
1           Text with some keywords
2           Text2 with one keyword
3           Text3 with three keywords

Table 2 
idText    idKey    sKeyword  
1           1       some
1           2       keywords
2           3       one
3           4       with
3           2       keywords
3           5       three

Is there any way to execute a nested replace among all the related keywords from Table2?

There are some solutions around like creating a function, but I do not think is a good solution because this is not going to be reused anywhere else. I did try a recursive CTE as well but without success.

The result must be something like this:

Table 1
idText     sText(nvarchar(500))
1           Text with Replaced_some Replaced_keywords
2           Text2 with Replaced_one keyword
3           Text3 Replaced_with Replaced_three Replaced_keywords

PS.

• The Replaced string is fixed. So you can use the string you prefer. The Replace sentence would be something like this: replace(sText, sKeyword, 'Replaced_' + sKeyowrd)
• IdKey is useless in this case, however it is part of our real DB structure

This is my failed attemp using a recursive CTE:

DECLARE @Table1 TABLE(  ID int,   sText nvarchar(200))
DECLARE @Table2 TABLE(  ID int,   sKeyword nvarchar(10))

INSERT INTO @Table1 VALUES(1, 'Text with some keywords')
INSERT INTO @Table1 VALUES(2, 'Text2 with one keyword')
INSERT INTO @Table1 VALUES(3, 'Text3 with three keywords')

INSERT INTO @Table2 VALUES(1, 'some')
INSERT INTO @Table2 VALUES(1, 'keywords')
INSERT INTO @Table2 VALUES(2, 'one')
INSERT INTO @Table2 VALUES(3, 'with')
INSERT INTO @Table2 VALUES(3, 'keywords')
INSERT INTO @Table2 VALUES(3, 'three')

;WITH CTE AS(

SELECT ID, sText FROM @Table1 
UNION ALL
SELECT c.ID, CAST(REPLACE(sText, sKeyword, 'New_' + sKeyword) AS nvarchar(200))  FROM CTE c 
INNER JOIN @Table2 t2 ON t2.ID = c.ID
) 
SELECT * FROM CTE 

The result is an infinite loop, it does not stop.

Any help will be appreciated

Answer 1

Disclaimer: Function slimmed down as promised, will update answer description accordingly in due time.

Per my current understanding of your problem, I think I can apply to it a function I designed to solve a more complex problem I had recently. There might be other solutions, but most certainly others can & will propose them, so why don't I offer you something a little less to be offered.

Be advised though, it was meant to address something more complex than yours (explained later), and right now I sadly don't have time to slim it down, but I'll get to that probably tomorrow. I hope the comments help. Irregardless, I'll summarize my function's objective for you:

There's a table that contains what messages to find, and what to replace them with. The function will receive a text value as input, will use a cursor to loop said table, and for each record in said table it will check if input text contains something to replace, and replace if applicable.

Two things to note about the original objective. First, there's a nested loop to address the scenario where a certain keyword exists multiple times, hence requiring multiple replacements. Second, I had to also deal with wildcards, variable lengths, and whether or not the replacement flag is set in discussed table. These two things plus others are probably the reason you'll find lots of weird material flying around.

CREATE FUNCTION [JACKINABOX](@TextToUpdate varchar(30), @FilterId int)
RETURNS varchar(30) AS
BEGIN

    DECLARE @Keyword varchar(30)

    DECLARE LonelyCursor CURSOR FOR
        SELECT Keyword FROM ReplacementInformation WHERE Id = @FilterId

    OPEN LonelyCursor ; FETCH NEXT FROM LonelyCursor INTO @Keyword

    WHILE @@FETCH_STATUS = 0 -- While there still remains keywords to process.
    BEGIN
        WHILE 1 = 1 -- Not sure, but I think this nested loop can be unlooped if [FETCH NEXT] was cut & pasted to replace [BREAK].
        BEGIN
            IF(CHARINDEX(@Keyword, @TextToUpdate) = 0)
                BREAK -- If cannot find current keyword anymore, move on to next keyword.
            ELSE -- Otherwise, update text then check again for same keyword.
                SET @TextToUpdate = REPLACE(@TextToUpdate, @Keyword, CONCAT('Replaced_', @Keyword))
        END
        FETCH NEXT FROM LonelyCursor INTO @Keyword
    END

    CLOSE LonelyCursor ; DEALLOCATE LonelyCursor

    RETURN @TextToUpdate

END