Alex Guerin Alex Guerin - 4 months ago 7x
SQL Question

Match comma separated values in column

If I have a column called 'Categories' with say

in the row comma-separated as shown, how would I match all rows with the category containing

I've tried a simple
but it is not quite accurate as there may be 'poo_science' which when searching for
would match both.

I've looked around StackOverflow and there are plenty of similar questions but all seem to want to return data as a comma separated list or something - not quite what I'm after.

I'd prefer not to use a stored procedure and cannot use full-text searching. I have a stored procedure I used which added another character
around each value and then would search for
is this too nasty? I'm after a little more simple method.


Disclaimer: The commentators are right... CSVs in a single field are a horrible design, and should be re-done.

With that said, here's how you can work around your problem:

Pad Categories with leading and trailing ,, that way you can include them in your wildcard search:

WHERE (',' + Categories + ',') LIKE '%,science,%'