‘Merge Fields’ – alike SQL Server function

I try to find a way to let the SGBD perform a population of merge fields within a long text.

Create the structure :

CREATE TABLE [dbo].[store]
(
    [id] [int] NOT NULL,
    [text] [nvarchar](MAX) NOT NULL
)

CREATE TABLE [dbo].[statement]
(
    [id] [int] NOT NULL,
    [store_id] [int] NOT NULL
)

CREATE TABLE [dbo].[statement_merges]
(
    [statement_id] [int] NOT NULL,
    [merge_field] [nvarchar](30) NOT NULL,
    [user_data] [nvarchar](MAX) NOT NULL
)

Now, create test values

INSERT INTO [store] (id, text) 
VALUES (1, 'Waw, stackoverflow is an amazing library of lost people in the IT hell, and i have the feeling that $$PERC_SAT$$ of the users found a solution, personally I asked $$ASKED$$ questions.')

INSERT INTO [statement] (id, store_id) 
VALUES (1, 1)

INSERT INTO [statement_merges] (statement_id, merge_field, user_data) 
VALUES (1, '$$PERC_SAT$$', '85%')

INSERT INTO [statement_merges] (statement_id, merge_field, user_data) 
VALUES (1, '$$ASKED$$', '12')

At the time being my app is delivering the final statement, looping through merges, replacing in the stored text and output

Waw, stackoverflow is an amazing library of lost people in the IT hell, and i have the feeling that 85% of the users found a solution, personally I asked 12 questions.

I try to find a way to be code-independent and serve the output in a single query, as u understood, select a statement in which the stored text have been populated with user data. I hope I’m clear.

I looked on TRANSLATE function but it looks like a char replacement, so I have two choices :

• I try a recursive function, replacing one by one until no merge_fields is found in the calculated text; but I have doubts about the performance of this approach;
• There is a magic to do that but I need your knowledge…

Consider that I want this because the real texts are very long, and I don’t want to store it more than once in my database. You can imagine a 3 pages contract with only 12 parameters, like start date, invoiced amount, etc… Everything else cant be changed for compliance.

Thank you for your time!

EDIT :

Thanks to Randy’s help, this looks to do the trick :

WITH cte_replace_tokens AS (

    SELECT replace(r.text, m.merge_field, m.user_data) as [final], m.merge_field, s.id, 1 AS i
    FROM store r
    INNER JOIN statement s ON s.store_id = r.id
    INNER JOIN statement_merges m ON m.statement_id = s.id
    WHERE m.statement_id = 1

    UNION ALL

    SELECT replace(r.final, m.merge_field, m.user_data) as [final], m.merge_field, r.id, r.i + 1 AS i
    FROM cte_replace_tokens r
    INNER JOIN statement_merges m ON m.statement_id = r.id
    WHERE m.merge_field > r.merge_field

) 

select TOP 1 final from cte_replace_tokens ORDER BY i DESC

I will check with a bigger database if the performance is good…

At least, I can “populate” one statement, I need to figure out to be able to extract a list as well.

Thanks again !

Answer

If a record is updated more than once by the same update, the last wins. None of the updates are affected by the others – no cumulative effect. It is possible to trick SQL using a local variable to get cumulative effects in some cases, but it’s tricky and not recommended. (Order becomes important and is not reliable in an update.)

One alternate is recursion in a CTE. Generate a new record from the prior as each token is replaced until there are no tokens. Here is a working example that replaces 1 with A, 2 with B, etc. (I wonder if there is some tricky xml that can do this as well.)

if not object_id('tempdb..#Raw') is null drop table #Raw
CREATE TABLE #Raw(
    [test] [varchar](100) NOT NULL PRIMARY KEY CLUSTERED,
)

if not object_id('tempdb..#Token') is null drop table #Token
CREATE TABLE #Token(
    [id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [token] [char](1) NOT NULL,
    [value] [char](1) NOT NULL,
)

insert into #Raw values('123456'), ('1122334456')
insert into #Token values(1, '1', 'A'), (2, '2', 'B'), (3, '3', 'C'), (4, '4', 'D'), (5, '5', 'E'), (6, '6', 'F');

WITH cte_replace_tokens AS (

    SELECT r.test, replace(r.test, l.token, l.value) as [final], l.id
    FROM [Raw] r
    CROSS JOIN #Token l
    WHERE l.id = 1

    UNION ALL

    SELECT r.test, replace(r.final, l.token, l.value) as [final], l.id
    FROM cte_replace_tokens r
    CROSS JOIN #Token l
    WHERE l.id = r.id + 1

) 
select * from cte_replace_tokens where id = 6