基于代码模式操作字符串的SQL算法

Question

我的Sql代码如下，

Declare @text nvarchar(max) = 'America,Japan,Indonesia,London'
Declare @Pattern nvarchar(20) = '1001'

对于这个模式，sql函数应该返回字符串'America,London'应根据模式操作String，如果模式字符为0，则应删除相应的逗号分隔文本。

期望样本输出:

@Pattern           @Text
1100               'America,Japan'
1000               'America'
0100               'Japan'
1111               'America,Japan,Indonesia,London'

在SQL Server 2008中实现这一结果的有效方法是什么

Answer 1

使用Parse/Split函数

Declare @text nvarchar(max) = 'America,Japan,Indonesia,London'
Declare @Pattern nvarchar(20) = '1001'
Select NewString = Stuff((Select  ',' +RetVal 
  From (Select A.RetSeq,A.RetVal 
         From (Select * from [dbo].[udf-Str-Parse](@Text,',')) A
         Join (Select * from [dbo].[udf-Str-Parse](Stuff(Replace(Replace(@Pattern,'0',',0'),'1',',1'),1,1,''),',')) B
           on (A.RetSeq=B.RetSeq and B.RetVal=1)
     ) A
  For XML Path ('')),1,1,'') 

返回

NewString
America,London

如果需要的话UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    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(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')