title: Is it possible to query a comma separated column for a specific value?
body: <p>I have (and don't own, so I can't change) a table with a layout similar to this. </p>
  
  <pre><code>ID | CATEGORIES
  ---------------
  1  | c1
  2  | c2,c3
  3  | c3,c2
  4  | c3
  5  | c4,c8,c5,c100
  </code></pre>
  
  <p>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</p>
  
  <p><code>SELECT id FROM table WHERE categories LIKE '%c2%';</code>
  Would return rows 2 and 3</p>
  
  <p><code>SELECT id FROM table WHERE categories LIKE '%c3%' and categories LIKE '%c2%';</code> Would again get me rows 2 and 3, but not row 4</p>
  
  <p><code>SELECT id FROM table WHERE categories LIKE '%c3%' or categories LIKE '%c2%';</code> Would again get me rows 2, 3, and 4</p>
  
  <p>I don't like all the <code>LIKE</code> statements. I've found <code>FIND_IN_SET()</code> in the Oracle documentation but it doesn't seem to work in 10g. I get the following error: </p>
  
  <pre><code>ORA-00904: "FIND_IN_SET": invalid identifier
  00904. 00000 -  "%s: invalid identifier"
  </code></pre>
  
  <p>when running this query: <code>SELECT id FROM table WHERE FIND_IN_SET('c2', categories);</code> (example from the docs) or this query: <code>SELECT id FROM table WHERE FIND_IN_SET('c2', categories) &lt;&gt; 0;</code> (example from Google)</p>
  
  <p>I would expect it to return rows 2 and 3.</p>
  
  <p>Is there a better way to write these queries instead of using a ton of <code>LIKE</code> statements?</p>