問題描述
假設(shè)我有 2 個(gè)表，其中都有名為 Brand 的列.該值以逗號(hào)分隔，例如，如果表之一具有
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
作為價(jià)值.然后另一個(gè)表有
as value. Then the other table has
HP,GIGABYTE
MICROSOFT
SAMSUNG,PHILIPS
作為值.
我想比較這些表以獲取所有匹配的記錄，在我的示例中 ACER,ASUS,HP 和 HP,GIGABYTE 匹配，因?yàn)閮烧叨加?HP.現(xiàn)在我正在使用循環(huán)來實(shí)現(xiàn)這一點(diǎn)，我想知道是否可以在單個(gè)查詢語法中做到這一點(diǎn).
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.
推薦答案
您想擺脫循環(huán)是正確的.
You are correct in wanting to step away from the loop.
自從您進(jìn)入 2012 年以來，String_Split() 不在討論范圍內(nèi).然而，有許多分裂/解析 TVF 函數(shù)在野中.
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
兩人都會(huì)回來
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,< & >',',')
這篇關(guān)于是否可以在不循環(huán)的情況下比較 T-SQL 中的逗號(hào)分隔字符串?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！