是否可以在不循环的情况下比较 T-SQL 中的逗号分-数据库问题

Is it possible to compare comma delimited string in T-SQL without looping?(是否可以在不循环的情况下比较 T-SQL 中的逗号分隔字符串?)

本文介绍了是否可以在不循环的情况下比较 T-SQL 中的逗号分隔字符串?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

假设我有 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,< & >',',')


                        这篇关于是否可以在不循环的情况下比较 T-SQL 中的逗号分隔字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！