Replace multiple strings in a single record using sql server

Question

I have an html string stored as a record:

<div>          
<p>##RecipientFirstName##,</p>          
<p>##CustomerProjectOwnerName## with ##CustomerName## has approved your...</p>         </div>

I want to replace the items inside the hash tags with actual names stored in the person table. I have tried this but it returns 3 different records. I need one record returned with the items inside the hash tags replaced with the Replace statement. My code:

SELECT mtg.Template,  REPLACE(mtg.Template, '##RecipientFirstName##',(SELECT p.FirstName FROM dbo.Person p WHERE p.PersonId = 16)) , 
                      REPLACE(mtg.Template, '##CustomerProjectOwnerName##',(SELECT p.FirstName FROM dbo.Person p WHERE p.PersonId = 16))   
 FROM dbo.MessageTypeGlobal mtg
WHERE mtg.MessageTypeGlobalId = 1

Zohar Peled · Accepted Answer · 2017-02-09 20:29:36Z

1

You need to nest your replace statements:

SELECT REPLACE(
               REPLACE(mtg.Template, '##RecipientFirstName##', p.FirstName), 
       , '##CustomerProjectOwnerName##', p.FirstName) 
FROM dbo.MessageTypeGlobal mtg
CROSS APPLY
(
    SELECT FirstName FROM dbo.Person WHERE PersonId = 16
) p
WHERE mtg.MessageTypeGlobalId = 1

I've used cross apply to prevent the need to write the subquery over and over again.

answered Feb 9, 2017 at 20:29

Zohar Peled

82.9k11 gold badges80 silver badges134 bronze badges

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

Comments

SqlZim · Accepted Answer · 2017-02-09 20:27:50Z

1

I think this is what you are trying to do (if you need different subqueries):

select customtemplate = replace(replace(replace(mtg.Template
    , '##CustomerProjectOwnerName##'
      , (select p.FirstName from dbo.Person p where p.PersonId = 16) )
    , '##RecipientFirstName##'
      , (select p.FirstName from dbo.Person p where p.PersonId = 16) )
    , '##CustomerName##'
      , (select p.FirstName from dbo.Person p where p.PersonId = 16) )
 from dbo.MessageTypeGlobal mtg
where mtg.MessageTypeGlobalId = 1

answered Feb 9, 2017 at 20:27

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Collectives™ on Stack Overflow

Replace multiple strings in a single record using sql server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related