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

Question

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 ... Lemon'

Clearly, the replace keyword does not do the job.

PS. If a solution required the format of the string to change in order to facilitate this, that is an option.

For example: '#1 / #9 ... #12' (to correlate with the earlier example)

In this format, perhaps we could break the string up into a rowset, based on #, take the left characters until we find a non-numeric, join to the testing table based on the numbers taken, replace the # and numbers with the testing table's display value then stuff all those individually modified tokens back into a single string for xml path?

I am using SQL Server 2016 which does not support string_agg. That said, if there is a solution using string_agg, I am still interested in reviewing it.

score 5 · Accepted Answer · answered May 02 '19 at 09:34

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   |

Peter Vandivier · Answer 2 · 2019-05-02T10:02:56.647

Assuming you're on a version that supports it (and going off the version in your original fiddle), you can use the native string_split() & string_agg() functions.

declare @id_list varchar(10) = '1,3'; -- for 'Apple,Celery' 
-- set @id_list = '3,4,5'; --for 'Celery,Dragonfruit,Eggplant'

select string_agg(display, ',') as agg
from (
    select t.display 
    from testing t
    cross apply string_split(@id_list,',') ss 
    where try_cast(ss.[value] as int) = t.id
) x;

The preceding example assumes you've ditched the curly braces on the way in and it's just a comma-separated list of numbers. If you want to keep the curly braces on the way in, you should try to enforce that it's coming in as well-formed JSON and use some of the native JSON functions for parsing it. The key bits on the above are:

provide an array of [Id]s to...
filter down to only those testing.displays you want and then...
...feed that text array in to string_agg()

Given the clarification that you're on 2016, and that string_agg() only becomes available in 2017, you can still use string_split() to create the array as needed and use one of the legacy approaches that work around the absence of string_agg() as you mention considering in the OP. For example:

select stuff(agg,1,1,'') as agg_trim_first_comma
from (
    select stuff(x.display,1,0,'') 
    from (
        select ',' + t.display 
        from testing t
        cross apply string_split('1,3',',') ss 
        where try_cast(ss.[value] as int) = t.id
    ) x (display )
    for xml path('')
) y (agg);

score 1 · Answer 3 · answered Apr 20 '23 at 10:16

Assuming you have chosen a scheme where the delimiting characters only appear as valid delimiters:

DECLARE @string nvarchar(max) = N'{1} / {9} ... {12}';
-- Compatible with SQL Server 2016
SELECT result =
(
    SELECT 
        [text()] = ISNULL(T.display, SS2.[value])
    FROM STRING_SPLIT(@string, N'}') AS SS
    CROSS APPLY STRING_SPLIT(SS.[value], N'{') AS SS2
    LEFT JOIN dbo.testing AS T
        ON T.id = TRY_CONVERT(integer, SS2.[value])
    FOR XML PATH (''), TYPE
).value('./text()[1]', 'nvarchar(max)');

Output:

result
Apple / Iceberg Lettuce ... Lemon

db<>fiddle

score 1 · Answer 4 · answered Apr 20 '23 at 19:24

Another solution that doesn't use STRING_SPLIT or STRING_AGG (comments inline):

-- Test string
DECLARE @string nvarchar(max) = N'abc.{1}./.{9}.&!&.{12}.xyz';
-- Previous statement must be terminated with a semicolon
WITH
    -- Itzik Ben-Gan's number generator
    L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    RN AS (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) AS n FROM L5),
    -- End number generator
    CharIdx AS
    (
        -- Numbers from 1 to the length of the input string
        SELECT RN.n
        FROM RN
        WHERE RN.n BETWEEN 1 AND LEN(@string)
    ),
    Ref AS
    (
        -- The reference table, with id converted to string symbol format
        -- e.g. id 1 -> '{1}'
        SELECT 
            symbol = FORMAT(T.id, N'{#}'),  -- Match the string symbol
            T.display 
        FROM dbo.testing AS T
    ),
    SymbolMatches AS
    (
        -- Find exact symbol matches in the input string
        SELECT
            pos_start = P.n, 
            pos_end = P.n + LEN(Ref.symbol) - 1,
            Ref.display
        FROM Ref
        JOIN CharIdx AS P
            -- Match symbol to the current string position
            ON SUBSTRING(@string, P.n, LEN(Ref.symbol)) = Ref.symbol
    ),
    Decode AS
    (
        SELECT
            I.n,
            result =
                -- Only two interesting cases:
                CASE
                    -- 1. Not part of an expansion (emit the single character)
                    WHEN SM.display IS NULL THEN SUBSTRING(@string, I.n, 1)
                    -- 2. Part of an expansion (emit the whole string once per match)
                    WHEN SM.pos_start = I.n THEN SM.display
                END
        FROM CharIdx AS I
        LEFT JOIN SymbolMatches AS SM
            -- Match every character in a substitution
            -- We only emit the match once, but need to
            -- know to ignore the rest
            ON I.n BETWEEN SM.pos_start AND SM.pos_end
        WHERE
            -- Filter to interesting cases above
            SM.display IS NULL
            OR SM.pos_start = I.n
    ),
    Result (as_xml) AS
    (
        -- STRING_AGG replacement for SQL Server 2016
        SELECT [text()] = Decode.result
        FROM Decode
        ORDER BY Decode.n
        FOR XML PATH (''), TYPE
    )
-- Handle entitization
SELECT result = Result.as_xml.value('./text()[1]', 'nvarchar(max)')
FROM Result;

result
abc.Apple./.Iceberg Lettuce.&!&.Lemon.xyz

score 0 · Answer 5 · answered Apr 20 '23 at 07:11

Here is a function that may help:

CREATE FUNCTION [Util].[REPLACE_KVPTABLE_JSONPATH] 
(
    @string NVARCHAR(MAX), 
    @json NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @String = REPLACE(REPLACE(REPLACE(@String, '`', '`!'), '{{', '`['), '}}', '`]')
;WITH inputs AS (
    SELECT 
        CONCAT('{',[key], '}') k , REPLACE(REPLACE(REPLACE([value], '`', '`!'), '{{', '`['), '}}', '`]') v
        FROM OPENJSON(@json, '$') WITH ([key] NVARCHAR(MAX) '$.key', [value] NVARCHAR(MAX) '$.value')
)
SELECT @String = REPLACE(@String, k COLLATE SQL_Latin1_General_CP1_CI_AS, v) FROM inputs

RETURN REPLACE(REPLACE(REPLACE(@String, '`]', '}}'), '`[', '{{'), '`!', '`')


END
GO
SELECT Util.REPLACE_KVPTABLE_JSONPATH('{3}, {4}, {5}', (SELECT id as [key], display as [value] FROM Id_Disaply FOR JSON PATH))

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

5 Answers5