Is it possible to query a comma separated column for a specific value?

Question

I have (and don't own, so I can't change) a table with a layout similar to this.

ID | CATEGORIES
---------------
1  | c1
2  | c2,c3
3  | c3,c2
4  | c3
5  | c4,c8,c5,c100

I need to return the rows that contain a specific category id. I starting by writing the queries with LIKE statements, because the values can be anywhere in the string

SELECT id FROM table WHERE categories LIKE '%c2%'; Would return rows 2 and 3

SELECT id FROM table WHERE categories LIKE '%c3%' and categories LIKE '%c2%'; Would again get me rows 2 and 3, but not row 4

SELECT id FROM table WHERE categories LIKE '%c3%' or categories LIKE '%c2%'; Would again get me rows 2, 3, and 4

I don't like all the LIKE statements. I've found FIND_IN_SET() in the Oracle documentation but it doesn't seem to work in 10g. I get the following error:

ORA-00904: "FIND_IN_SET": invalid identifier
00904. 00000 -  "%s: invalid identifier"

when running this query: SELECT id FROM table WHERE FIND_IN_SET('c2', categories); (example from the docs) or this query: SELECT id FROM table WHERE FIND_IN_SET('c2', categories) <> 0; (example from Google)

I would expect it to return rows 2 and 3.

Is there a better way to write these queries instead of using a ton of LIKE statements?

Answer 1

You can, using LIKE. You don't want to match for partial values, so you'll have to include the commas in your search. That also means that you'll have to provide an extra comma to search for values at the beginning or end of your text:

select 
  * 
from
  YourTable 
where 
  ',' || CommaSeparatedValueColumn || ',' LIKE '%,SearchValue,%'

But this query will be slow, as will all queries using LIKE, especially with a leading wildcard.

And there's always a risk. If there are spaces around the values, or values can contain commas themselves in which case they are surrounded by quotes (like in csv files), this query won't work and you'll have to add even more logic, slowing down your query even more.

A better solution would be to add a child table for these categories. Or rather even a separate table for the catagories, and a table that cross links them to YourTable.