Where value in column containing comma delimited v

Question

Hi I am wondering how 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?

Thanks for any help anyone can give.

Cheers.

Answer 1

Since you don't know how many comma-delimited entries you can find, you may need to create a function with 'charindex' and 'substring' SQL Server functions. Values, as returned by the function could be used in a 'in' expression.

You function can be recursively invoked or you can create loop, searching for entries until no more entries are present in the string. Every call to the function uses the previous found index as the starting point of the next call. The first call starts at 0.