Is it possible to compare comma delimited string in T-SQL without looping?

Question

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

as values.

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.

There is no elegant or efficient way to do this because it is fundamentally not how an RDBMS is designed to work. Each of those values should be alone on its own row in a table - then all this becomes stupidly easy to query. If at all possible you should redesign accordingly. — Alex K., Mar 20 '18 at 12:31
It can be achieved without a loop and in a single query, but you will need a string split function to apply on both ends and join afterwards. If you are using SQL Server 2016+ you can use `STRING_SPLIT` function. — EzLo, Mar 20 '18 at 12:34
There are plenty on splinters on the internet that don't use a while loop. Some use Tally Tables, some XML, and you have CLR functions as well. Also, as @EzequielLópezPetrucci just said (and beat me to), SQL Server 2016 has a built in `STRING_SPLIT` function. — Thom A, Mar 20 '18 at 12:35
Well, the correct solution would be to normalize your database. Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** If that's impossible, use a string splitting function. — Zohar Peled, Mar 20 '18 at 12:38
@SalmanA: No, I'm using 2012. And I can't simply change the design and drop the entire comma delimited column. — tickwave, Mar 20 '18 at 12:43

John Cappelletti · Accepted Answer · 2018-03-20T13:19:59.777

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

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

Example 1 - without a 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

Example 2 - with a 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

Both Would Return

T1_Brand        T2_Brand
ACER,ASUS,HP    HP,GIGABYTE

The UDF if interested

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

Lemon · Answer 2 · 2018-03-20T13:16:18.080

Had the same problem with comparing "," delimited strings

you can use "XML" to do that and compare the outputs and return the same/different value:

declare  @TestInput nvarchar(255)
, @TestInput2 nvarchar(255)

set @TestInput = 'ACER,ASUS,HP'
set @TestInput2 = 'HP,GIGABYTE'



;WITH FirstStringSplit(S1) AS
(
 SELECT CAST('<x>' + REPLACE(@TestInput,',','</x><x>') + '</x>' AS XML)
)
,SecondStringSplit(S2) AS
(
SELECT CAST('<x>' + REPLACE(@TestInput2,',','</x><x>') + '</x>' AS XML)
 )

 SELECT STUFF(
 (
SELECT ',' + part1.value('.','nvarchar(max)')
FROM FirstStringSplit
CROSS APPLY S1.nodes('/x') AS A(part1)
WHERE part1.value('.','nvarchar(max)') IN(SELECT B.part2.value('.','nvarchar(max)')
                                              FROM SecondStringSplit 
                                              CROSS APPLY S2.nodes('/x') AS B(part2)
                                              ) 
FOR XML PATH('')
),1,1,'') as [Same Value]

Edit:

Changed 'Stuff' to 'XML'

What does `FOR XML PATH('')` do in this query? It effectively takes each separate row of results in the result set defined by the `SELECT` clause and causes them to be concatenated together. What does `STUFF(...,1,1,'')` do? It removes the leading comma from that result. Calling this method `STUFF` is to give the credit entirely to the wrong tool. — Damien_The_Unbeliever, Mar 20 '18 at 13:10

Is it possible to compare comma delimited string in T-SQL without looping?

2 Answers2

Linked