This can also be done with recursive SQL, although I can't say if it's a good idea to do so. I did add an ID column to your replacement map table. To test the code I generated 456976 four character strings:
CREATE TABLE #ReplacementMap (
ID INT NOT NULL IDENTITY (1, 1),
old NVARCHAR(10),
new NVARCHAR(10),
PRIMARY KEY (ID)
);
INSERT INTO #ReplacementMap VALUES ('A',5);
INSERT INTO #ReplacementMap VALUES ('C',9);
INSERT INTO #ReplacementMap VALUES ('D',4);
CREATE TABLE #String1 (
ID INT NOT NULL IDENTITY (1, 1),
string1 NVARCHAR(100)
);
WITH ALL_LETTERS AS (
SELECT distinct CHAR(number) LETTER
FROM master..spt_values
WHERE number >= 65 AND number <= 90
)
INSERT INTO #String1 WITH (TABLOCK)
SELECT a1.LETTER + a2.LETTER + a3.LETTER + a4.LETTER
FROM ALL_LETTERS a1
CROSS JOIN ALL_LETTERS a2
CROSS JOIN ALL_LETTERS a3
CROSS JOIN ALL_LETTERS a4;
Here is the code that does the translation:
WITH rec_cte AS (
SELECT
s.ID
, REPLACE(s.string1, rm.old, rm.new) new_string1
, 1 replace_id
FROM #String1 s
INNER JOIN #ReplacementMap rm ON rm.ID = 1
UNION ALL
SELECT
s.ID
, REPLACE(s.new_string1, rm.old, rm.new) new_string1
, replace_id + 1
FROM rec_cte s
INNER JOIN #ReplacementMap rm ON rm.ID = replace_id + 1
)
SELECT ID, new_string1
FROM rec_cte
WHERE replace_id = (SELECT COUNT(*) FROM #ReplacementMap);
Suppose you have S rows in #String1 and R rows in #ReplacementMap. For each row in #ReplacementMap we do a join to the table, filter to the next row, and REPLACE() using that row. Once there are no more rows in #ReplacementMap the full result set of S X R rows is returned. That is filtered down to the final translation by the subquery. The code will do S X R REPLACE() operations and R + 1 joins to a single row result set, along with some internal tempdb operations.
This should work without any modifications as long as you have less than 101 replacement strings. The code seems to perform similarly to the solution posted by Adán Bucio. On my machine this query finished in about 10 seconds and his solution finished in 20 seconds. However, you should not pick your solution on that basis. You should use whatever code you are most comfortable with, as long as it meets your response time requirements.
Note that SQL Server 2017 has a built-in function that makes this kind of operation trivial: TRANSLATE.
