How can I replace multiple times based on rows from a query?

Question

I have a table with strings containing 0 to many "blanks" consisting of three underscores. In the same table, I'll have a string representing the words I want to go into those spaces separated by a delimiter.

For example: ___! My name is ___. I am ___ to see you! and Hello|PrinceTyke|happy.

I already have a function written in-house that will give me a row for each word in the delimited list as well as the position in the string.

1 | Hello
2 | PrinceTyke
3 | happy

How can I replace my blank-filled string with those words in order and end up with "Hello! My name is PrinceTyke. I am happy to see you!"?

Edit:

I am using SQL Server 2016 and would like to perform this kind of replacement on sets of rows at a time.

I realized that I didn't fully communicate my problem.

In one table, I have both the original string with "blanks" as well as the string of characters I want to do the replacement with.

Id | RelatedId | Text
----------------------------------------------------------
1  | NULL      | ___! My name is ___. I am ___ to see you!
2  | 1         | Hello|PrinceTyke|happy

I have another table that I want to store the output of this replacement in.

Id | OtherTableId | TextOutput
------------------------------------------------------------------------
1  | 1            | Hello! My name is PrinceTyke. I am happy to see you!

This is still slightly simplified, but I hope it's more clear.

Fix your string so the positions are clear: '@1! My name is @2. I am @3 to see you!. Then there is some hope of handling this. — Gordon Linoff
– Gordon Linoff, Commented Jan 8, 2020 at 13:39
@GordonLinoff Your suggestion helps, but if doing this replacement on multiple records, then we still have another problem. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 8, 2020 at 13:47
SQL Server 2016. The answer below using STUFF works for one string at a time, but Tim is right, I would like a solution to do this replacement on sets at a time if possible. I'm open to changing my structure around to make it work, I'm just having a hard time wrapping my head around this. — PrinceTyke
– PrinceTyke, Commented Jan 8, 2020 at 13:52

lptr · Accepted Answer · 2020-01-09 09:20:49Z

3

declare @s varchar(200) = '___! My name is ___. I am ___ to see you!'
declare @t table
(
    rowid int,
    thetext varchar(20)
);

insert into @t(rowid, thetext)
values (1, 'Hello'), (2, 'PrinceTyke'), (3, 'happy');


select @s = stuff(@s, charindex('___', @s), len('___') , thetext)
from @t
order by rowid;

select @s;

Demo

for max 20 replacement values, hardcoded pivot up to 20 positions and FORMATMESSAGE()

declare @t table
( 
id int,
relatedid int,
thetext varchar(200)
);

insert into @t (id, relatedid, thetext)
values (1, null, '___! My name is ___. I am ___ to see you!'),
(2, 1, 'Hello|PrinceTyke|happy');


select a.*, b.*, 
FORMATMESSAGE(replace(a.thetext, '___', '%s') , 
        v.[1], v.[2], v.[3], v.[4], v.[5], v.[6], v.[7], v.[8], v.[9], v.[10],
        v.[11], v.[12], v.[13], v.[14], v.[15], v.[16], v.[17], v.[18], v.[19], v.[20]
) AS Result
from @t as a
join @t as b on a.id = b.relatedid
cross apply 
(
    select 
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
        [11], [12], [13], [14], [15], [16], [17], [18], [19], [20]
    from 
    (
        select value,row_number() over(order by (select null)) as rownum
        from string_split(b.thetext, '|')
    ) as s
    pivot
    (
        max(value) for rownum in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20])
    ) as p
) as v

--or create a scalar function (using the stuff() approach)

create or alter function dbo.inplace_replace(@s varchar(1000), @values varchar(200))
returns varchar(1000)
with returns null on null input
as
begin
    declare @d datetime=getdate(); -- :)

    select @s = stuff(@s, charindex('___', @s), len('___') , value)
    from string_split(@values, '|')
    order by row_number() over(order by (select null));

    return(@s);
end
go

--test

declare @t table
( 
id int,
relatedid int,
thetext varchar(200)
);

insert into @t (id, relatedid, thetext)
values (1, null, '___! My name is ___. I am ___ to see you!'),
(2, 1, 'Hello|PrinceTyke|happy');


select a.*, b.*, dbo.inplace_replace(a.thetext, b.thetext) as result
from @t as a
join @t as b on a.id = b.relatedid;

edited Jan 9, 2020 at 9:20

answered Jan 8, 2020 at 13:41

lptr

6,8582 gold badges9 silver badges16 bronze badges

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

5 Comments

PrinceTyke Over a year ago

This works on one row at a time, but I would like to perform this replacement on multiple rows at a time.

Tim Biegeleisen Over a year ago

SQL Server is not the best tool for this job. You should be doing this in an application language like Java or C#.

lptr Over a year ago

@PrinceTyke, it might not be highly performant, you could create a scalar function and use that for multiple-rows.

PrinceTyke Over a year ago

I'm worried that I'll end up having to move this process into C# for the sake of performance, but this idea in a scalar function is at least functional.

lptr Over a year ago

maybe this string manipulation/replacement is better suited for another language than SQL. Alternatively, if you already know that there is going to be a maximum number of values (a|b|c), you could hard-code a pivot up to "max number" and use FORMATMESSAGE(). Answer, edited... (no performance testing whatsoever)

Collectives™ on Stack Overflow

How can I replace multiple times based on rows from a query?

1 Answer 1

Demo

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related