Search code examples
sqlsql-serverseparator

SQL Server where condition on column with separated values

I have a table with a column that can have values separated by ",". Example column group:

id column group:

1   10,20,30
2   280
3   20

I want to create a SELECT with where condition on column group where I can search for example 20 ad It should return 1 and 3 rows or search by 20,280 and it should return 1 and 2 rows.

Can you help me please?

Solution

  • As pointed out in comments,storing mutiple values in a single row is not a good idea..

    coming to your question,you can use one of the split string functions from here to split comma separated values into a table and then query them..

    create table #temp
(
id int,
columnss varchar(100)
)

insert into #temp
values
(1,'10,20,30'),
(2,   '280'),
(3,   '20')

select * 
from #temp
cross apply
(
select * from dbo.SplitStrings_Numbers(columnss,',')
)b
where item in (20)

id  columnss    Item
1   10,20,30    20
3   20          20