问题描述
假设我有 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 中的逗号分隔字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！