Multiple String REPLACEs with T-SQL CTEs

I had to solve the problem to update data in a table of NVARCHAR strings where several substrings had to be replaced with their respective counterparts, or translations.

Of course the easy way is to apply as many REPLACE functions as you need, but why not try a more generic way?

The string table in its simplest form looks like this:

CREATE TABLE #Data (
	data NVARCHAR(50)
)

INSERT INTO #Data (data) VALUES ('banana')
INSERT INTO #Data (data) VALUES ('apples')

and these are the translations:

CREATE TABLE #T (
    old	NVARCHAR(50),
    new NVARCHAR(50)
)

INSERT INTO #T VALUES('p', 'Q')
INSERT INTO #T VALUES('s', 'Z')
INSERT INTO #T VALUES('a', 'A')

I came up with the following CTE statement:

The CTE stores the original strings, their intermediate translations, and their final translations. A level counter is used to find the entry with the highest count of translations for each string, indicating the final result.

The CTE starts with the original data in all three columns, and level set to zero.

From the original data, all REPLACE results of all translatable texts are calculated. Replacing strings are compared using a case-sensitive collation:

WITH CTE (org, calc, DATA, level) AS
(
    SELECT data, data, data, 0
    FROM	#Data

    UNION ALL

    SELECT CTE.org, CTE.data,
        CONVERT(NVARCHAR(50), REPLACE(CTE.data, #T.old, #T.new)), CTE.level + 1
    FROM	CTE
    INNER JOIN #T ON CTE.data LIKE '%' + #T.old + '%'
        COLLATE Latin1_General_CS_AS
)
SELECT DISTINCT org, data, level
FROM CTE
WHERE level =
    (SELECT MAX(level) FROM CTE c WHERE CTE.org = c.org)

Finally, clean up the temp tables:

DROP TABLE #Data
DROP TABLE #T

This code returns the expected result:

org	data	level
apples	AQQleZ	3
banana	bAnAnA	1