Alex Guerin Alex Guerin - 5 months ago 10
SQL Question

Match comma separated values in column

If I have a column called 'Categories' with say

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

I've tried a simple
LIKE
but it is not quite accurate as there may be 'poo_science' which when searching for
'%science%'
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
'$value$'...
is this too nasty? I'm after a little more simple method.

Answer

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,%'
Comments