SQL Question

Search in SQL Server comma string

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.

Answer

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.