是否可以在不循环的情况下比较 T-SQL 中的逗号分隔字符串?

2021-09-10 00:00:00 split sql tsql sql-server

假设我有 2 个表，其中都有名为 Brand 的列.该值以逗号分隔，例如，如果表之一具有

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

作为值.

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

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.

推荐答案

您想摆脱循环是正确的.

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

自从您进入 2012 年以来，String_Split() 不在讨论范围内.然而，有许多分裂/解析 TVF 函数在野中.

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

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

相关文章