title: Where value in column containing comma delimited values
body: <p>I wish to write an SQL statement for SQL Server 2008 that Selects entry's where a column contains a value, now the value within the column is a comma delimited list (usually - there could only be one entry (and no leading comma)) so what In checking for is "is this value contained somewhere within the list?", for instance:</p>
  
  <pre><code>COLUMN = Cat, Dog, Sparrow, Trout, Cow, Seahorse
  Does COLUMN contain Cat? YES
  Does COLUMN contain horse? NO
  Does COLUMN contain Sheep? NO
  </code></pre>
  
  <p>or</p>
  
  <pre><code>COLUMN = Mouse
  Does COLUMN contain Hare? NO
  Does COLUMN contain Mouse? YES
  </code></pre>
  
  <p>etc</p>
  
  <p>I was thinking I could use the 'IN' keyword as such</p>
  
  <pre><code>SELECT id_column FROM table_name WHERE 'Cat' IN COLUMN
  </code></pre>
  
  <p>but this does not work as it seems that you can only use that to check if a column contains one of a series of comma delimited values.</p>
  
  <p>I also cannot use CONTAINS() OR 'LIKE' as this, in the above example would return values for 'horse' as the whole string contains horse in 'Seahorse', and I can't search for the needle plus a comma (if I'm looking for 'horse' the search would be 'horse,') as what if the entry is at the end of a the list?  And I can't search for a comma plus a needle (if I'm looking for 'horse' the search would be ',horse')
  as what if the entry is the first in the list? And I can't use both as what if the entry is the only (single) entry?</p>