Multiple String Search and Replace on SQL Server (for templates)

Question

I have a database table that has template html, formatted as "text {var1} text {othervar}". I would like to apply the string substitutions in a more pragmatic way than:

set @template = replace(@template, '{var1}', field.value);
set @template = replace(@template, '{othervar}', field.othervalue);
-- notice I don't have matching template variables and data fields

There are dozens of variables and hundreds of subjects to apply the template against. I would rather do this outside of SQL, but that's not an option.

I came up with the following but I get incorrect syntax near TABLE. I ran this on Microsoft SQL Server version 10.50.2500 (2008). The DB was set to 90 compatibility (2005), but even after I changed it to 100 (SQL Server 2008) I received the same error.

CREATE FUNCTION applyTemplate 
(
  @subject nvarchar(max),
  @replacements as TABLE (
    search nvarchar(64),
    replacement nvarchar(max)
  )
)
RETURNS nvarchar(max)
AS BEGIN
  DECLARE @return nvarchar(max)
  set @return = @subject

  select @return = replace(@return, search, replacement) from @replacements
  RETURN @return
END

SELECT applyTemplate(
  'Hello, {planet}! Welcome to the {galaxy}.', 
  (select '{planet}','World' union select '{galaxy}', 'MilkyWay')
)

The real source data query going into the applyTemplate function probably wouldn't come from a union like this, and might be a correlated subquery ..

select 
  person.email,
  applyTemplate(
    template.body,
    (select n,v from person_vars pv where pv.person=person.id)
  ) as body 
from template 
cross join person 
where template.id = 1

What will fix the function so that it works as intended or is there just a better approach (hoping they don't involve dynamic query building).

Based on: http://msdn.microsoft.com/en-us/library/bb675163.aspx `CREATE TYPE dbo.templateVarsType AS TABLE ( search nvarchar(64) primary key, replacement nvarchar(max) )` Then replaced the second parameter with `@replacements templateVarsType READONLY ` but: SELECT dbo.applyTemplate( 'Hello, {planet}! Welcome to the {galaxy}.', (select '{planet}','World' union '{galaxy}','MilkyWay') ) Msg 116, Level 16, State 1, Line 4 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. — Marques Johansson, Sep 12 '13 at 13:29

fateen · Answer 1 · 2017-09-20T05:49:49.280

2

-- Applying a template using a single replace
DECLARE @return NVARCHAR(MAX) = 'Hello, {planet}! Welcome to the {galaxy}.';

SELECT @return = REPLACE(@return, [search], [replacement])
FROM
(VALUES 
  ('{planet}', 'World')
, ('{galaxy}', 'MilkyWay')
) AS templateVars([search], [replacement]);

SELECT @return;

edited Sep 20 '17 at 05:49

answered Sep 20 '17 at 05:33

fateen

21
3

2

Code only answers should come with a few lines of explanations – Matthias Seifert Sep 20 '17 at 05:57

score 1 · Accepted Answer · answered Sep 12 '13 at 15:50

1

DECLARE @t AS templateVarsType

INSERT INTO @t
SELECT '{planet}','World'
UNION
SELECT '{galaxy}','MilkyWay'

SELECT dbo.applyTemplate( 'Hello, {planet}! Welcome to the {galaxy}.'
                         , @t )

Check this fiddle http://sqlfiddle.com/#!3/edda3/10

answered Sep 12 '13 at 15:50

JoseTeixeira

1,296
8
14

That works albeit not as flexible as I was hoping. Can you have a look at http://sqlfiddle.com/#!3/eba56/13/0 for something less contrived. Why can't I use a correlated subquery as the table-valued-parameter (or can I)? (I'll give you the answer, but I was hoping for more.) I don't understand why the examples seem to indicate that you can use a subquery as the parameter, but here you had to assign it to a variable first (which prevents comparison against the outer tables). Thanks. – Marques Johansson Sep 12 '13 at 18:39
In that fiddle I used a query like the second one I provided in the original question. `SELECT dbo.applyTemplate('Hello, {planet}! Welcome to the {galaxy}.', (SELECT n,v from planet_variables where planet.id=planet_id) ) from planet where id > 0` based on `CREATE TABLE planet ( id int primary key, name varchar(64)) CREATE TABLE planet_variables (planet_id int , n varchar(64), v varchar(max)); insert into planet values (1, 'Earth'), (2,'Glep Glop');insert into planet_variables values (1,'{planet}','Earth'),(1,'{galaxy}','MilkyWay'), (2,'{planet}','Gleep Glop'),(2,'{galaxy}','Glopulon')` – Marques Johansson Sep 12 '13 at 18:45
It's also worth noting that I have tried replacing @t in the function parameter list with `cast((SELECT n,v from planet_variables where planet.id=planet_id) as templateVarsType)` .. but no joy. – Marques Johansson Sep 12 '13 at 18:58
1

Ain't being able to open your fiddle, but here is a better explanation: [call a function with a user defined table type parameter](http://ask.sqlservercentral.com/questions/95050/is-it-possible-to-call-a-function-with-a-user-defi.html) – JoseTeixeira Sep 13 '13 at 17:16

Multiple String Search and Replace on SQL Server (for templates)

2 Answers2