Kanapuli Kanapuli - 1 month ago 18
SQL Question

SQL Algorithm to Manipulate a string based on a Code Pattern

My Sql Code is as follows ,

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


So For this Pattern the sql function should return the string as 'America,London'
The String should be manipulated based on the pattern and the corresponding comma seperated text should be removed if the pattern character is 0.

Expected Sample Output:

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


What is the Efficient way to acheive this results in SQL Server 2008

Answer

With the help of a Parse/Split Function

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,'') 

Returns

NewString
America,London

The UDF if needed

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',' ')