SQL Question

Search in SQL Server comma string

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

For example :

The column

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
, or both. In this case,
A, C, T
must be returned.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download