How to make a nested replacement 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 a way to do a nested replacement among all the related keywords from Table2 ?

There are several solutions around creating a function, but I don’t think it is a good solution, because it will not be reused anywhere. I also tried a recursive CTE, but to no avail.

The result should 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.

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

This is my failed attemp using 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 endless cycle, it does not stop.

Any help would be appreciated

Answer 1

Disclaimer: the function is smoothed, as promised, will timely update the description of the response.

In accordance with my current understanding of your problem, I think I can apply a function to it that I developed to solve a more complex problem that I recently had. There may be other solutions, but, of course, others can and will offer them, so why not offer me something less that can be offered.

Be careful, however, it was intended to solve something more complex than yours (explained later), and now, unfortunately, I do not have time to shorten it, but I will get to that, probably tomorrow. I hope the comments help. To no avail, I will summarize my function for you:

Here is a table that contains the messages that need to be found, and what to replace them with. The function will receive a text value as an input signal, will use the cursor to cycle the specified table, and for each record in the specified table it will check whether the input text contains something to replace, and replace if applicable.

Two things to note the original goal. Firstly, there is a nested loop to solve the scenario where a certain keyword exists several times, so several replacements are required. Secondly, I also had to deal with variable-length wildcards and set the replacement flag in the table under discussion. These two things plus the other are probably the reason that you will find a lot of weird stuff 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