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: 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
– Marques Johansson, Commented Sep 12, 2013 at 13:29

fateen · Accepted Answer · 2017-09-20 05:49:49Z

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, 2017 at 5:49

answered Sep 20, 2017 at 5:33

fateen

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matthias Wünsch Over a year ago

Code only answers should come with a few lines of explanations

JoseTeixeira · Accepted Answer · 2013-09-12 15:50:47Z

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, 2013 at 15:50

JoseTeixeira

1,2968 silver badges15 bronze badges

4 Comments

Marques Johansson Over a year ago

That works albeit not as flexible as I was hoping. Can you have a look at 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 Over a year ago

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 Over a year ago

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.

JoseTeixeira Over a year ago

Ain't being able to open your fiddle, but here is a better explanation: call a function with a user defined table type parameter

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related