Kanapuli Kanapuli - 1 year ago 81
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 Source

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



The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
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',' ')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download