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

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

假设有两个表，都有一个名为Brand的列。值是逗号分隔的，例如，如果其中一个表有

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.

我想要比较这些表，以获得所有匹配的记录，在我的例子中，宏碁，华硕，惠普和惠普，千兆的匹配，因为两者都有HP。现在我正在使用循环来实现这一点，我想知道是否可以用一个查询语法来实现这一点。

2 个解决方案

#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.

由于您是在2012年，String_Split()已经离开了表。但是，有许多分割/解析TVF函数。

Example 1 - without a 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

Example 2 - with a TVF

例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

Both Would Return

都将返回

T1_Brand        T2_Brand
ACER,ASUS,HP    HP,GIGABYTE

The UDF if interested

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

#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:

您可以使用“XML”进行此操作，并比较输出并返回相同/不同的值:

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'

“东西”改为“XML”

#1