Find and replace multiple text string in sql server

Hrides Thakur

8y1.6k

Hi All

I have one table name EmailTemplate in that table i have a column EmailContent where i have data like below:

Dear users,

__REQUEST_TYPE__ number: __NUMBER__

Customer: __OEM_GROUP__

Region/Division: __WORKFLOW_DIVISION__

Program: __PROGRAM__

Product line: __PRODUCT_LINE__

has been approved.

This __REQUEST_TYPE__ was submitted with the following ratios:

With the following validation comments:

__COMMENT_LIST__

when i select the this data, i want to replace all these bold contents e.g __REQUEST_TYPE__ with some values which i will get from another table in my select statements.

i want to avoid this kind of exercise below:

select replace(replace(replace(TemplateContent,'__REQUEST_TYPE__','Weekly'),'__NUMBER__','Twice Weekly'),'__OEM_GROUP__','china') as content from mytable;

please let me know suitable solution.

thanks.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Arvind Singh8y ago
Accepted answer
Hi
declare a variable @content and set your div data in this variable (your emailcontent data).
get your all these type " __REQUEST_TYPE__" value in another select statement
your select should return data like this:
replacething value
__REQUEST_TYPE__ somevalue
__NUMBER__ 12345
__OEM_GROUP__ yourgroup

loop through the above select statement retuen data and till last row.
write replace function inside the loop and send your @content, replacething, value
e.g.
while(lastrow)
begin
set @content= REPLACE(@content, replacething[i], value[i])
end

syntax my not correct, its only to give you idea to solve your problem

thanks,
Hrides Thakur8y ago
Thanks for replying @Nitin and @Arvind
Nitin Sontakke8y ago
In my previous enguagement, I had done pretty much same. But to the best of my knowledge, there is no short cut.

I had discourage nesting the calls. You are better of making separate call each time. Best is to write a UDF to which you will pass two separate delimited strings. One with find terms and another with replace terms. Then perform find replace in a loop.

replacething	value
__REQUEST_TYPE__	somevalue
__NUMBER__	12345
__OEM_GROUP__	yourgroup