Where value in column containing comma delimited values

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:

COLUMN = Cat, Dog, Sparrow, Trout, Cow, Seahorse
Does COLUMN contain Cat? YES
Does COLUMN contain horse? NO
Does COLUMN contain Sheep? NO

or

COLUMN = Mouse
Does COLUMN contain Hare? NO
Does COLUMN contain Mouse? YES

etc

I was thinking I could use the 'IN' keyword as such

SELECT id_column FROM table_name WHERE 'Cat' IN COLUMN

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.

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?

There is one tricky scenario. If I am looking for '40' in the list '17,34,400,12' then it would find ",40" and return that incorrect entry. This takes care of all solutions:

WHERE (',' + RTRIM(MyColumn) + ',') LIKE '%,' + @search + ',%'

WHERE
      MyColumn LIKE '%,' + @search + ',%' --middle
      OR
      MyColumn LIKE @search + ',%' --start
      OR
      MyColumn LIKE '%,' + @search --end
      OR 
      MyColumn =  @search --single (good point by Cheran S in comment)

SELECT * FROM TABLENAME WHERE FIND_IN_SET(@search, column)

If it turns out your column has whitespaces in between the list items, use

SELECT * FROM TABLENAME WHERE FIND_IN_SET(@search, REPLACE(column, ' ', ''))

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html