TSQL: Replace same field multiple times in one que

Question

Given a table of replacements, can all the replacements be applied to a column in a table in the same query?

Disclaimer: I can do this with a cursor, or with dynamic sql to create a nested string of replacements; I want to know if it could be done concisely.

I have a replacements table,

create table #replacements(old varchar(max), new varchar(max))
insert into #replacements values
('X','Y'),
('A','B'),
('W','V'),
('C','D')

and a table of values to replace:

create table #value(value varchar(max))
insert into #value values
('XA'),
('WC')

I'd like to perform these in one query which gives back:

YB

VD

Is there any way to do this? I tried,

update v
set value = replace(value,old,new)
from #value v,
#replacements

but this gives (only the first row in the full join is done):

YA

WC

Answer 1

Temp tables for data sample

IF OBJECT_ID('Tempdb..#replacements') IS NOT NULL 
    DROP TABLE #replacements
IF OBJECT_ID('Tempdb..#value') IS NOT NULL 
    DROP TABLE #value;

CREATE TABLE #replacements
    (
      old VARCHAR(MAX) ,
      new VARCHAR(MAX)
    )
INSERT  INTO #replacements
VALUES  ( 'X', 'Y' ),
        ( 'A', 'B' ),
        ( 'W', 'V' ),
        ( 'C', 'D' )
CREATE TABLE #value ( value VARCHAR(MAX) )
INSERT  INTO #value
VALUES  ( 'XA' ),
        ( 'WC' )

Data before update

---

Solution using dynamic query

DECLARE @Query AS VARCHAR(MAX)
SET @Query = 'UPDATE #value SET Value = '
    + ( SELECT  REPLICATE('REPLACE(', ( SELECT  COUNT(*) - 1
                                        FROM    #replacements
                                      )) + 'REPLACE(value'
                + ( SELECT  ',''' + r.old + ''' ,''' + r.new + ''') '
                    FROM    #replacements AS r
                  FOR
                    XML PATH('')
                  ) AS R
      )

EXEC  (@Query)

Data after update

Answer 2

JBond's answer is simple but it's SLOW for any large number of rows since it will be RBAR. His function has to grab each value one by one and then run it through your replacement table. With a large number of rows, you'll see some serious performance issues.

Here's a dynamic query that is, in my opinion, a little simpler than Vasily's, although both really do the same thing. The code should perform really well for any number of rows. Try it out:

DECLARE @Replace VARCHAR(MAX);

SELECT @Replace = COALESCE('REPLACE(' + @Replace,'REPLACE(value') + ',''' + old + ''',''' + new + ''')'
FROM #replacements


EXEC
(
    'UPDATE #value
    SET Value = ' + @Replace
)

Recursive Solution

WITH CTE_replacements
AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) row_num,
            old,
            new
    FROM #replacements
),
CTE_recursion
AS
(
    SELECT  REPLACE(value,old,new) AS value,
            1 AS cnt
    FROM #value
    CROSS APPLY (SELECT old,new FROM CTE_replacements WHERE row_num = 1) CA

    UNION ALL

    SELECT  REPLACE(value,old,new) AS value,
            cnt + 1
    FROM cte_recursion A
    CROSS APPLY (SELECT old,new FROM CTE_replacements WHERE row_num = cnt + 1) CA

)

SELECT TOP(SELECT COUNT(*) FROM #value) *
FROM CTE_recursion
ORDER BY 2 DESC
OPTION (MAXRECURSION 0)

Compared to the dynamic SQL solution, this is not great. Compared to the function, it is better. What the recursion does is apply each change one by one to the entire dataset. So the row in replacement is applied to all the data. Then the second change(row) is applied to the entire dataset etc... So for a small number of changes because it goes through it RBAR, and then a not too large value set, it will work just fine.

Answer 3

I am giving this answer based on the assumption that the example is only using temporary tables for our convenience.

However, you could use a scalar function that does this for you.

Note - As mentioned above. This assumes that the table in the function does not need to be a temporary table.

Function:

CREATE FUNCTION dbo.ReplaceCharacters (@value VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    SELECT @value = REPLACE(@value, r.old, r.new)
    FROM replacements r --Assumes this is no longer a temp table

    RETURN @value
END

This way you can just call the function directly from the #value.

View the result set:

SELECT
    v.value CurrentValue,
    dbo.ReplaceCharacters(v.value) ReplacedValue
FROM #value v

Output:

+--------------+----------------+
| CurrentValue | ReplacedValue |
+--------------+----------------+
|     XA       |     YB         |
|     WC       |     VD         |
+--------------+----------------+

Apply the changes:

UPDATE #value
SET value = dbo.ReplaceCharacters(value)