Is it possible to compare comma delimited string i

问题:

Let's say I have 2 tables where both has column called Brand. The value is comma delimited so for example if one of the table has

ACER,ASUS,HP  
AMD,NVIDIA,SONY

as value. Then the other table has

HP,GIGABYTE  
MICROSOFT  
SAMSUNG,PHILIPS

as values.

I want to compare these table to get all matched record, in my example ACER,ASUS,HP and HP,GIGABYTE match because both has HP. Right now I'm using loop to achieve this, I'm wondering if it's possible to do this in a single query syntax.

回答1:

You are correct in wanting to step away from the loop.

Since you are on 2012, String_Split() is off the table. However, there are any number of split/parse TVF functions in-the-wild.

Example 1 - without a TVF

Declare @T1 table (Brand varchar(50))
Insert Into @T1 values 
('ACER,ASUS,HP'),
('AMD,NVIDIA,SONY')

Declare @T2 table (Brand varchar(50))
Insert Into @T2 values 
('HP,GIGABYTE'),
('MICROSOFT'),
('SAMSUNG,PHILIPS')


Select Distinct
       T1_Brand = A.Brand
      ,T2_Brand = B.Brand
 From ( 
        Select Brand,B.*
         From  @T1
         Cross Apply (
                        Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace(Brand,',','</x><x>')+'</x>' as xml)) as A 
                        Cross Apply x.nodes('x') AS B(i)
                     ) B
      ) A
 Join ( 
        Select Brand,B.*
         From  @T2
         Cross Apply (
                        Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace(Brand,',','</x><x>')+'</x>' as xml)) as A 
                        Cross Apply x.nodes('x') AS B(i)
                     ) B
      ) B
 on A.RetVal=B.RetVal

Example 2 - with a TVF

Select Distinct
       T1_Brand = A.Brand
      ,T2_Brand = B.Brand
 From ( 
        Select Brand,B.*
         From  @T1
         Cross Apply [dbo].[tvf-Str-Parse](Brand,',') B
      ) A
 Join ( 
        Select Brand,B.*
         From  @T2
         Cross Apply [dbo].[tvf-Str-Parse](Brand,',') B
      ) B
 on A.RetVal=B.RetVal

Both Would Return

T1_Brand        T2_Brand
ACER,ASUS,HP    HP,GIGABYTE

The UDF if interested

CREATE FUNCTION [dbo].[tvf-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((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[tvf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[tvf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[tvf-Str-Parse]('this,is,<test>,for,< & >',',')

回答2:

Had the same problem with comparing "," delimited strings

you can use "XML" to do that and compare the outputs and return the same/different value:

declare  @TestInput nvarchar(255)
, @TestInput2 nvarchar(255)

set @TestInput = 'ACER,ASUS,HP'
set @TestInput2 = 'HP,GIGABYTE'



;WITH FirstStringSplit(S1) AS
(
 SELECT CAST('<x>' + REPLACE(@TestInput,',','</x><x>') + '</x>' AS XML)
)
,SecondStringSplit(S2) AS
(
SELECT CAST('<x>' + REPLACE(@TestInput2,',','</x><x>') + '</x>' AS XML)
 )

 SELECT STUFF(
 (
SELECT ',' + part1.value('.','nvarchar(max)')
FROM FirstStringSplit
CROSS APPLY S1.nodes('/x') AS A(part1)
WHERE part1.value('.','nvarchar(max)') IN(SELECT B.part2.value('.','nvarchar(max)')
                                              FROM SecondStringSplit 
                                              CROSS APPLY S2.nodes('/x') AS B(part2)
                                              ) 
FOR XML PATH('')
),1,1,'') as [Same Value]

Edit:

Changed 'Stuff' to 'XML'