sql - 如何根据查询中的行多次替换？

Question

我有一个包含 0 到多个由三个下划线组成的“空白”的字符串的表。在同一张表中，我将有一个字符串表示我想要进入由分隔符分隔的那些空格的单词。

例如：___! My name is ___. I am ___ to see you!和Hello|PrinceTyke|happy。

我已经有一个内部编写的函数，它将为分隔列表中的每个单词以及字符串中的位置提供一行。

1 | Hello
2 | PrinceTyke
3 | happy

如何按顺序用这些单词替换我的空白字符串并以“你好！我的名字是 PrinceTyke。我很高兴见到你！”结束？

编辑：

我正在使用 SQL Server 2016，并希望一次对多组行执行这种替换。

我意识到我没有完全传达我的问题。

在一张表中，我既有带有“空格”的原始字符串，也有我想要替换的字符串。

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

我有另一个表，我想在其中存储此替换的输出。

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

这仍然略微简化，但我希望它更清楚。

标签： sqlsql-serverstring

Answer 1

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;

演示

最多 20 个替换值，硬编码枢轴最多 20 个位置和 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

-- 或者创建一个标量函数（使用 stuff() 方法）

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

- 测试

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;