SQL Server where condition on column with separated values

Question

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?

Don't store data as comma separated items. It will only cause you lots of trouble. One value per row is the SQL way! — jarlh, Aug 12 '16 at 08:46
In fact, after answering I realized this is a very common topic and there must be a lot of answers already in SO. Indeed searching for [`sql split string`](http://stackoverflow.com/search?q=sql+split+string) will turn up with numerous Q/As some of which are very detailed and insightful. — P. Kouvarakis, Aug 12 '16 at 09:08

P. Kouvarakis · Answer 1 · 2016-08-12T09:08:39.243

The short answer is: don't do it.

Instead normalize your tables to at least 3NF. If you don't know what database normalization is, you need to do some reading.

If you absolutely have to do it (e.g. this is a legacy system and you cannot change the table structure), there are several articles on string splitting with TSQL and at least a couple that have done extensive benchmarks on various methods available (e.g. see: http://sqlperformance.com/2012/07/t-sql-queries/split-strings)

Since you only want to search, you don't really need to split the strings, so you can write something like:

SELECT id, list
FROM t
WHERE ','+list+',' LIKE '%,'+@searchValue+',%'

Where t(id int, list varchar(max)) is the table to search and @searchValue is the value you are looking for. If you need to search for more than one value you have to add those in a table and use a join or subquery.

E.g. if s(searchValue varchar(max)) is the table of values to search then:

SELECT distinct t.id, t.list
FROM t INNER JOIN s 
       ON ','+t.list+',' LIKE '%,'+s.searchValue+',%'

If you need to pass those search values from ADO.Net consider table parameters.

score 1 · Accepted Answer · edited May 23 '17 at 12:32

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

SQL Server where condition on column with separated values

2 Answers2