假设我有 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 中的逗号分隔字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持html5模板网!