替换结果集中的占位符内容

Question

>我有这个表格作为一个简单的例子，食物项目：

Table: FoodItem
1 Burgers
2 French Fries
3 Pizzas

我还有另一张表格，上面有这样的短语：

Table: Phrase
1 I want {1} and {2}!
2 I just want {3}.

我想创建一个 sp，它从短语表中获取所有短语，并将占位符部分替换为食物表中的内容，如下所示：

I want Burgers and French Fries!
I just want Pizzas.

我怎样才能做到这一点？我已经尝试了"喜欢"和"patindex"，但我不确定这些是否适合这项任务。

Answer 1

对于少量替换和少量数据，您可以使用递归 CTE（在执行大量替换时，我看到性能不佳）。像这样：

Declare @Phrase table (ID int,Phrase varchar(100))
Insert into @Phrase values 
 (1,'I want {1} and {2}!')
,(2,'I just want {3}.')
,(3,'i just don not like {1} and {3}');
Declare @FoodItem table (ID int, MapTo varchar(100))
Insert Into @FoodItem values 
 (1 ,'Burgers')
,(2 ,'French Fries')
,(3 ,'Pizza');
With DataSource AS
(
    SELECT ID
           ,Phrase
           ,1 as level
    FROM @Phrase
    UNION ALL
    SELECT DS.[ID]
          ,cast(REPLACE(ds.Phrase, '{'+ CAST(DS.[Level] AS VARCHAR(8)) +'}', FI.[MapTo])  as varchar(100))
          ,level + 1 as level
    FROM DataSource DS
    INNER JOIN @FoodItem FI
        ON DS.[level] = FI.[ID]
)
SELECT *
FROM DataSource
WHERE level = (SELECT max(id) from @FoodItem) + 1;

我相信这可以进一步改进。

如果要处理大量数据，最好实现SQL CLR函数来替换多个字符串并连接字符串。

因此，对于每一行，您将拥有如下所示的内容：

 (1,'I want {1} and {2}!', '{1}|{2}','Burgers|French Fries')
,(2,'I just want {3}.', '{3}', 'Pizza')
,(3,'i just don not like {1} and {3}', '{1}|{3}','Burgers|Pizza');

然后你的函数接受三列并在内部执行替换。

Answer 2

示例

Declare @Phrase table (ID int,Phrase varchar(100))
Insert into @Phrase values 
 (1,'I want {1} and {2}!')
,(2,'I just want {3}.')
Declare @FoodItem table (ID int, MapTo varchar(100))
Insert Into @FoodItem values 
 (1 ,'Burgers')
,(2 ,'French Fries')
,(3 ,'Pizza')

Select A.ID
      ,NewStr = replace(replace(B.S,' ||',''),'|| ','')
 From  @Phrase A
 Cross Apply (
                Select S = Stuff((Select ' ' +coalesce(MapTo,RetVal)
                  From (
                        Select RetSeq = Row_Number() over (Order By (Select null))
                              ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(replace(replace(A.Phrase,'{','|| {'),'}','} ||'),' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
                       ) B1
                   Left Join @FoodItem B2 on B1.RetVal = concat('{',B2.ID,'}')
                  Order by RetSeq
                  For XML Path ('')),1,1,'') 
             ) B

返回

ID  NewStr
1   I want Burgers and French Fries!
2   I just want Pizza.

编辑 - 创建一个 UDF 可能会执行类似 以后

Declare @S varchar(max) = 'I want {1} and {2}!'
Select @S = replace(@S,concat('{',ID,'}'),MapTo)
  From  FoodItem 
Select @S

返回

I want Burgers and French Fries!