Compare comma seperated values in sql server
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
please: NO points
select test2.* from test2
inner join test1
on ',' + test1.col1 + ',' like '%,' + convert(varchar,test2.col2
{+edits, sorry}
select test2.* from test2
inner join test1
on ',' + test1.col1 + ',' like '%,' + convert(varchar,test2.col2
{+edits, sorry}
good catch PorletPaul,
I missed that the asker mentioned that test2.col2 was int. So indeed casting to varchar is required
I missed that the asker mentioned that test2.col2 was int. So indeed casting to varchar is required
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select col2 from test2
inner join test1 on ',' + col1 + ',' like '%,' + col2 + ',%'