sql server, replace chars in string with values in table-CodePudding

how can i replace values in string with values that are in a table?

for example

    select *
into #t
from 
(
    select 'bla'c1,'' c2 union all
    select 'table'c1,'TABLE' c2 union all
    select 'value'c1,'000' c2 union all
    select '...'c1,'' c2
)t1

declare @s nvarchaR(max)='this my string and i want to replace all values that are in table #t'

i have some values in my table and i want to replace C1 with C2 in my string.

the results should be

this my string and i want to replace all 000 that are in TABLE #t

CodePudding user response：

You can do this via recursion. Assuming you have a table of find-replace pairs, you can number the rows and then use recursive cte:

create table #t(c1 nvarchar(100), c2 nvarchar(100));
insert into #t(c1, c2) values
('bla', ''),
('table', 'table'),
('value', '000'),
('...', '');

declare @s nvarchar(max) = 'this my string and i want to replace all values that are in table #t';

with ncte as (
    select row_number() over (order by (select null)) as rn, *
    from #t
), rcte as (
    select rn, replace(@s, c1, c2) as newstr
    from ncte
    where rn = 1

    union all

    select ncte.rn, replace(rcte.newstr, ncte.c1, ncte.c2)
    from ncte
    join rcte on ncte.rn = rcte.rn   1
)
select *
from rcte
where rn = 4