How to replace multiple parts of a string with data from multiple rows?

• Here's a fiddle to show what I'm after.

Given a table with two columns - an integer ID and a text-based string - I want to start with a string value that encodes any number of integers wrapped in curly braces, mixed in with any other valid text characters.

Example: '{1} / {9} ... {12}'

With a single SELECT statement, I want to return a string whereby all the integers (and their wrapping braces) have been replaced with a value derived from my table; specifically, the text value for the row having an ID that matches the number found in the source string.... and any characters outside the curly braces remain untouched.

Here is an example that fails to complete the task:

select
  replace('{13} {15}','{'+cast(id as varchar)+'}',isNull(display,''))
from testing;

This would return 1 row per row in the testing table. For the row with id value = 13, the '{13}' portion of the string is successfully replaced, but the '{15}' portion is not (and vice versa on row 15).

I imagine creating a function that loops through all testing rows and repeatedly attempts replacements would solve the problem. Be that as it may, a straight-up SQL statement would be preferable to looping.

Example Data

+----+-------------------+
| id |  display          |
+----+-------------------+
|  1 |  Apple            |
|  2 |  Banana           |
|  3 |  Celery           |
|  4 |  Dragonfruit      |
|  5 |  Eggplant         |
|  6 |  Fenugreek        |
|  7 |  Gourd            |
|  8 |  Honeydew         |
|  9 |  Iceberg Lettuce  |
| 10 |  Jackfruit        |
| 11 |  Kale             |
| 12 |  Lemon            |
| 13 |  Mandarin         |
| 14 |  Nectarine        |
| 15 |  Olive            |
+----+-------------------+

Example use cases

```
select replace('{1} {3}',null,null)
-- Returns 'Apple Celery'

select replace('{3},{4},{5}',null,null);
-- Returns 'Celery,Dragonfruit,Eggplant'

select replace('{1} / {9} ... {12}',null,null);
-- Returns 'Apple / Iceberg Lettuce ..

Here is an example of using a recursive cte to translate the variables

drop table if exists testing;
go
create table testing (id int, display varchar(16));
insert into testing values (1, 'Apple');
insert into testing values (2, 'Banana');
insert into testing values (3, 'Celery');
insert into testing values (4, 'Dragonfruit');
insert into testing values (5, 'Eggplant');

DROP FUNCTION IF EXISTS dbo.TranslateVariables
go
CREATE FUNCTION dbo.TranslateVariables
(
    @StringValue VARCHAR(MAX)
)
RETURNS TABLE

AS
RETURN (

--Common Table Expression for Translation
WITH TranslationTable
AS (
    SELECT FindValue = '{' + convert(varchar(5),id) + '}' ,ReplaceValue = display,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM testing
    )
--Recursive CTE to loop through the TranslationTable and replace FindValue with ReplaceValue
,RecursiveCte as
(
SELECT @StringValue AS StrValue
    ,(
        SELECT count(*)
        FROM TranslationTable
        ) AS cnt

UNION ALL

SELECT replace(StrValue, tt.FindValue, tt.Replacevalue)
    ,cnt - 1
FROM RecursiveCte
JOIN TranslationTable tt
    ON tt.rn = cnt )

SELECT StrValue
    ,cnt
FROM RecursiveCte where cnt = 0
    )
go

--Verify translation
SELECT *
FROM dbo.TranslateVariables('{1} {3}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

StrValue     | cnt |
|--------------|-----|
| Apple Celery | 0   |

SELECT *
FROM dbo.TranslateVariables('{3},{4},{5}')
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

| StrValue                    | cnt |
|-----------------------------|-----|
| Celery,Dragonfruit,Eggplant | 0   |

StackExchange Database Administrators Q#237182, answer score: 5