Compare arrays in SQL

yo, i want to pick rows from a table with the criteria that at least one of the element in a string that is comma separated is the same as at least one of the element in a field with the same kind of string.

for example:

i insert the string "red, green, blue"

in the table there is:

RowID MyStringValue
1 | "red,white,black"
2 | "white,yellow,gray"
3 | "black,blue,green"

i want with a select statement get row 1 and 3, but not 2. In vb.net i would have done something like this.



How do i write a function like that in SQL, that i later on can use in my WHERE-statement. i have full access to the database and all type of stored procedures and etc is no worry. btw, i work in mssql 2005

build User Function:
http://www.sqlteam.com/article/user-defined-functions
good luck!

Or, you could rethink your table design, e.g. if you had a parent table and a child table that contained your colours, something like:

CREATE TABLE ParentTable(ID int identity(1, 1) not null, SomeOtherColumn int)

CREATE TABLE ChildTable(ParentTableID int not null, Colour varchar(10) not null) --not showing foreign key...

Then your stored proc could just do something like insert into a temp table and do a straight forward join to the child table and pull back all parent id's (or parent rows if required). This would be super quick and you may find other benefits with having your colour data split out like this.