How to get rows containing value in comma seprated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get rows containing value in comma seprated

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-24 : 05:40:54

Hi Friends,

I have a table like:
ID VALUES
1 15,14,10
2 4,3,2,1
3 5,6,3,2
-- -----
-- -----

How to select ID which has 2 in VALUES column?

Please try to solve it ASAP as I'm just stuck and watching my monitor for solution.

Thanks in advance.
Pushp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-24 : 05:56:38

[code]
where ',' + VALUES + ',' LIKE '%,2,%'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-24 : 06:26:33

this will return values for 12 22 as well I need exact actually

ohhh sorry i did not noticed "," after %
thanks
please help one step more how to pass 2 as parameter?
and thanks for this help

quote:
Originally posted by khtan

where ',' + VALUES + ',' LIKE '%,2,%'

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-24 : 06:30:55

[code]
declare @tbl table
(
[ID] int,
[VALUES] varchar(10)
)

insert into @tbl select 1, '15,2,14,10'
insert into @tbl select 2, '12,13,14'
insert into @tbl select 3, '21,22,23'

select *
from @tbl
where ',' + [VALUES] + ',' like '%,2,%'

/* RESULT
ID VALUES
----------- ----------
1 15,2,14,10

(1 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-24 : 06:49:12

Ok I was able to do it as :

declare @i int
set @i=2
select * from TABLE where VALUES
LIKE '%,'+cast(@i as varchar) +',%' or VALUES LIKE cast(@i as varchar) +',%'
or VALUES LIKE '%,'+cast(@i as varchar)

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources