Using SQL REPLACE where the replaced string and replacement are the result of another replacement

Question

Trying to replace some strings on my database where I've got two tables. The replacement on table_2 uses the results of the first replacement as an input:

Current state (string is only a stand-in, it can be anything, the important part is the dash):

|table_1 - col1|    |table_2   - col1            |       
----------------    ------------------------------
|string-1      |    |text string-1 text string-3 |     
|string-2      |    |text string-3 string-2 t-ext|
|string-3      |    |string-2 text string-3 te-xt|

Desired Result:

|table_1 - col1 |    |table_2 - col1              |       
-----------------    ------------------------------
|string_1       |    |text string_1 text string_3 |     
|string_2       |    |text string_3 string_2 t-ext|
|string_3       |    |string_2 text string_3 te-xt|

Simply put I want to replace the - with _ in table_1 and also perform a corresponding replacement on table_2.

Came up with the first part, but I can't figure out the replacement part on table_2:

SELECT col1, REPLACE(col1, '-', '_') as Replacement  
FROM table_1 
where col1 like '%-%'

I need to do something like this (this code is INCORRECT):

SELECT REPLACE(col1, 
    SELECT [col1] FROM [table_1] where col1 like '%-%',
    SELECT REPLACE([col1], '-', '_') FROM [table_1] where col1 like '%-%')      
from table_2

Answer 1

One way of doing it with Dynamic query. Replace the actual table name and column names (commented where to change).

DECLARE @colNames VARCHAR(MAX) = ''
SELECT @colNames = @colNames + ', [' + table1_Col1 + ']' FROM tableName1 -- Table1 Column and Table1 Name
DECLARE @ReqColNames VARCHAR(MAX) = STUFF(@colNames, 1, 1, '')

DECLARE @int int
SELECT @int = count(*) FROM tableName1 -- Table1 Name
DECLARE @replace varchar(max) = replicate('REPLACE(', @int) + 't2.table2_Col2' -- Table2 Column

DECLARE @replaceCols varchar(max) = ''
SELECT @replaceCols = @replaceCols + ', r.[' + table1_Col1 + '], replace(r.[' + table1_Col1 + '], ''-'', ''_''))' FROM tableName1 -- Table1 Column and Table1 Name
DECLARE @ReplaceString varchar(max) = @replace + @replaceCols


DECLARE @cmd varchar(max) = 'SELECT ' +  @ReplaceString + ' FROM
(
    SELECT * FROM tableName1
    PIVOT
    (MAX (table1_Col1) FOR table1_Col1 IN (' + @ReqColNames + ')) x
) r
CROSS JOIN tableName2 t2'

EXEC(@cmd)

Static Query: for above code (to show what the above dynamic code is generating):

Select replace(replace(replace(t2.table2_Col2
        , r.[string-1], replace(r.[string-1], '-', '_'))
        , r.[string-2], replace(r.[string-2], '-', '_'))
        , r.[string-3], replace(r.[string-3], '-', '_'))
from
(
    Select * from tableName1
    PIVOT
    (MAX (table1_Col1) FOR table1_Col1 IN ([string-1], [string-2], [string-3])) x
) r
CROSS JOIN tableName2 t2

Output:

text string_1 text string_3
text string_3 string_2 t-ext
string_2 text string_3 te-xt

Answer 2

Variable based replacement can be done as replacing with a table.

DECLARE @Raw NVARCHAR(MAX) = '...';
SELECT @Raw= REPLACE(@Raw, P, R)
FROM (VALUES ('string-1', 'string_1'),
            ('string-2','string_2'),
            ('string-3','string_3'),
            ('string-4','string_4'),
) AS T(P, R);

To execute the same logic against table, think about some statements like

SELECT col1, MultipleReplace(col1, replacement_table(P, R))
FROM some_table

So create a function that accepts a string input and a replacement table. In order to pass table to function, we have to create a table type.

CREATE TYPE dbo.MulReplacements AS TABLE 
(
    Pattern NVARCHAR(MAX) NOT NULL,
    Replacement NVARCHAR(MAX) NOT NULL
)

Then the function would be

CREATE FUNCTION dbo.MulReplace(
    @string AS NVARCHAR(MAX),
    @replacements AS dbo.MulReplacements READONLY
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result AS NVARCHAR(MAX) = @string;

    SELECT @result = REPLACE(@result, R.Pattern, R.Replacement) 
    FROM @replacements AS R;

    RETURN @result;
END

Put all together

DECLARE @replacement AS dbo.MulReplacements;

INSERT INTO @replacement
SELECT col1, REPLACE(col1, '-', '_')
FROM (VALUES ('string-1'), ('string-2'), ('string-3')) AS table_1(col1)

SELECT col1, dbo.Mulreplace(col1, @replacement)
FROM (VALUES ('text string-1 text string-3'), ('text string-3 string-2 t-ext'), ('string-2 text string-3 te-xt')) AS table_2(col1)

Answer 3

This is fully ad-hoc, no recursion needed:

DECLARE @table_1 TABLE(ID INT IDENTITY,col1 VARCHAR(100));
INSERT INTO @table_1 VALUES    
 ('string-1'),('string-2'),('string-3');

DECLARE @table_2 TABLE(ID INT IDENTITY,col1 VARCHAR(100));
INSERT INTO @table_2 VALUES    
 ('text string-1 text string-3'),('text string-3 string-2 t-ext'),('string-2 text string-3 te-xt');

--The first CTE replaces the value in t1

WITH t1New AS
(
    SELECT ID AS t1_ID
          ,t1.col1 AS t1c1
          ,REPLACE(t1.col1,'-','_') AS new_t1c1 
    FROM @table_1 AS t1
)

--The second CTE splits the strings of t2 on the blanks

,t2Splitted AS
(
    SELECT ID
          ,col1 AS t2c1
          ,CAST('<x>' + REPLACE(t2.col1,' ','</x><x>') + '</x>' AS XML) AS Casted
    FROM @table_2 AS t2
)

--This CTE maps the values to the splitted parts

,Mapped AS
(
    SELECT t1New.*
          ,t2Splitted.ID AS t2_ID 
          ,ROW_NUMBER() OVER(PARTITION BY t2Splitted.ID ORDER BY (SELECT NULL)) AS PartIndex
          ,part.value('.','nvarchar(max)') AS Part
    FROM t2Splitted
    CROSS APPLY t2Splitted.Casted.nodes('/x') AS A(part)
    LEFT JOIN t1New ON t1New.t1c1=part.value('.','nvarchar(max)')
)

--If there is a mapping, the new value is taken, else take the old value

,NewValues AS
(
    SELECT *
           ,CASE WHEN t1c1 IS NOT NULL THEN new_t1c1 ELSE Part END AS newValue
    FROM Mapped 
)

--The final CTE re-concatenates the string with blanks in the original order

,Final AS
(
    SELECT nv1.t2_ID
      ,(SELECT ' ' + nv2.newValue
        FROM NewValues AS nv2
        WHERE nv2.t2_ID=nv1.t2_ID
        ORDER BY PartIndex
        FOR XML PATH('')) AS FinalValue
    FROM NewValues AS nv1
    GROUP BY nv1.t2_ID
)

--This last value is used to update the original table

UPDATE t2 SET t2.col1=Final.FinalValue
FROM @table_2 AS t2
INNER JOIN Final ON Final.t2_ID=t2.ID

What's up to you: UPDATE t1, that's a one-liner and get rid of the trailing space in FinalValue :-)

SELECT * FROM @table_2

Answer 4

For no more then 2 replacements

SELECT t2.col2, REPLACE(REPLACE(t2.col2,t1.col1,REPLACE(t1.col1, '-', '_')),t3.col1,REPLACE(t3.col1, '-', '_')) 
FROM table_2 t2
JOIN table_1 t1 ON t2.col2 like '%' +t1.col1+'%' AND t1.col1 LIKE '%-%'
LEFT JOIN table_1 t3 ON t3.col1 <> t1.col1 AND t2.col2 LIKE '%'+t3.col1+'%' AND t3.col1 LIKE '%-%'
WHERE t2.col2 LIKE '%-%'