是否可以在T-SQL中比较逗号分隔的字符串而不循环？

Question

假设我有2个表，两个表的列都称为Brand。该值以逗号分隔，因此，例如，如果表之一具有

ACER,ASUS,HP  
AMD,NVIDIA,SONY

作为价值。然后另一个表有

HP,GIGABYTE  
MICROSOFT  
SAMSUNG,PHILIPS

作为价值。

我想比较这些表以获取所有匹配的记录，在我的示例ACER,ASUS,HP和HP,GIGABYTEmatch中，因为两者都有HP。现在，我正在使用循环来实现这一点，我想知道是否有可能在单个查询语法中做到这一点。

Answer 1

您想脱离循环是正确的。

由于您在2012年，因此String_Split（）不在桌面上。但是，野生中有任意数量的拆分/解析TVF函数。

示例1-没有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

示例2-使用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

两者都会回来

T1_Brand        T2_Brand
ACER,ASUS,HP    HP,GIGABYTE

UDF（如果有兴趣）

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