Solved

Compare comma seperated values in sql server

Posted on 2013-05-30
6
300 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 49

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

734 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