Search in SQL Server comma string

Question

I need to perform a search in a SQL Server comma string.

For example :

The column type has values "A, C, T" and the user passes through an app the values "M, T".

I need to return all rows containing at least M, T, or both. In this case, A, C, T must be returned.

Don't store data as comma separated items. It will only cause you lots of trouble. — jarlh, Sep 09 '16 at 09:30
Split your value first (http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns) and then compare it — Prisoner, Sep 09 '16 at 09:32

score 1 · Accepted Answer · edited Sep 09 '16 at 11:50

1

The correct solution is to fix your data structure, with one type per row. You should not be storing multiple values in a single column.

But, sometimes we are stuck with other people's bad design decisions. You can use like to solve this:

where ', ' + type + ', ' like '%, M, %' or
      ', ' + type + ', ' like '%, T, %'

The trick here is to wrap commas round type, so that 'M' and 'T' will also be found when they are the start or end of type.

edited Sep 09 '16 at 11:50

Jan Doggen

answered Sep 09 '16 at 10:06

Gordon Linoff

Thank you! It's a very old database and we can't change it's structure. I think the best solution would be a column by type but... :) – Pablo Rodrigo Alves da Silva Sep 09 '16 at 10:35

1 Answers1