Solved

Compare comma seperated values in sql server

Posted on 2013-05-30
6
280 Views
Last Modified: 2013-09-19
Hi,
  I have a tow tables
Test1 has a varchar column
it has data like  
col1
1,2,3,4,5,6,7,8

I have another table test2
It has a int column
Col2
1
2
3
4
5

I want to compare like below

select col2 from test2 where col2 in(select col1 from test1)

Please let me know how can I compare comma seperated varchar to int

Thank you
0
Comment
Question by:Sthokala
6 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 39208246
a quick way could be:

select col2 from test2
inner join test1 on ',' + col1 + ',' like '%,' + col2 + ',%'
0
 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 167 total points
ID: 39208344
hi Sthokala,

I created this example, might be overkill but basically converts it into an xml then uses xquery to split it into rows...

Anway it was a bit of fun doing it so here you go

Working Example
http://sqlfiddle.com/#!3/a02b9/1

DECLARE @X xml

SET @X =
(SELECT 
cast('<X>'+replace(col1,',','</X><X>')+'</X>' as XML) as xmlfilter from test1)

select Id,col2 from test2 where
col2 in (
SELECT N.value('.', 'varchar(10)') as value 
FROM 
@X.nodes('X') as T(N)
)

Open in new window


CREATE TABLE TEST1(ID int ,col1 varchar(30))

INSERT INTO TEST1(ID ,col1) VALUES('1','1,2,3,4,5,6,7,8');

CREATE TABLE TEST2(ID varchar(30) ,col2 int)

INSERT INTO TEST2(ID ,Col2) VALUES('t1','1');
INSERT INTO TEST2(ID ,Col2) VALUES('t2','2');
INSERT INTO TEST2(ID ,Col2) VALUES('t3','3');
INSERT INTO TEST2(ID ,Col2) VALUES('t4','4');
INSERT INTO TEST2(ID ,Col2) VALUES('t5','5');

Open in new window

0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 166 total points
ID: 39208515
You can also try like this.
SELECT * FROM Test2 WHERE Col2 IN (
SELECT ltrim(SUBSTRING(col1, n, CHARINDEX(',', col1 + ',',n) - n))
   FROM Test1
   CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
   WHERE SUBSTRING(',' + col1, n, 1) = ','
   AND n < LEN(col1) + 1)

Open in new window


http://sqlfiddle.com/#!3/a02b9/3
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39209830
please: NO points

select test2.* from test2
inner join test1
on ',' + test1.col1 + ',' like '%,' + convert(varchar,test2.col2) + ',%' ;

{+edits, sorry}
0
 
LVL 41

Expert Comment

by:ralmada
ID: 39210532
good catch PorletPaul,

I missed that the asker mentioned that test2.col2 was int. So indeed casting to varchar is required
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 167 total points
ID: 39361027
unfortunately, the col1 from test1 is not actually a comma separated list of values that you can use in the "IN ()" clause.

To do it the way you want (and assuming only 1 row in test1) then you could do some dynamic SQL

declare @sql varchar(8000)
set @sql = (select 'select col2 from test2 where col2 in ('+col1+')' from test1)
print @sql
-- the print will show 
-- select col2 from test2 where col2 in (1,2,3,4,5,6,7,8)
exec (@sql)
-- and if multiple rows in test1 then it fails with an error

Open in new window

So, hopefully the above shows you the subtle difference.

But the above requires multi-steps, a bit like the XML solution. Nothing wrong with multiple step solutions, just has to be wrapped up as a procedure (or script) of some kind and can be a lot more robust because it can also accommodate processing logic.

Now, as others above have suggested, you need to start doing pattern matches. And because you want '2' to only match ',2,' and not '22' (etc) then you have to string together the pattern properly, or, unpivot the values in test1.

Some advantages with unpivotting first is when test1 has multiple rows. It can be done via XML or it can be done using the system table spt_values trick.

Either that, or use an "EXISTS" clause such as :

select col2 
from test2 
where exists (select NULL from test1 where ','+col1+',' like '%,'+left(col2,4)+',%')

Open in new window


or using the magic numbers to first expand the list of numbers in col1 (as an alternative to sharath's)

;with col1_cte as
(SELECT distinct ltrim(SUBSTRING(col1, n, CHARINDEX(',', col1 + ',',n) - n)) as col1
 FROM Test1
 CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
 WHERE SUBSTRING(',' + col1, n, 1) = ','
 AND n < LEN(col1) + 1) 
-- we can now do the original query but using the upivotted values via col1_cte
SELECT col2 from test2 where col2 in (select col1 from col1_cte)

Open in new window


that magic numbers thingy is fully described in : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1221-Fun-with-MS-SQL-spt-values-for-delimited-strings-and-virtual-calendars.html

But the real challenge is "what else" is being used in that query as to filters / other columns / rows in test1 etc because that can change the approach fairly significantly and you should be looking at your execution plans as you go because there has been a number of different solutions by the experts above.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now