I have this type of record:
Note that using a junction table will usually perform better as noted in the comments.
Nevertheless, assuming you are stuck with the design:
TableA ID ValueList 1 Uno,Dos,Tres 2 Foo,Bar,Baz,Quux
And you want to do the equivalent of this:
Select * from TableA a where @Value in ValueList -- ERROR
Select * from TableA a where ','+ValueList+',' like '%,'+@Value+',%'
If you want to do this:
select * from TableA b where b.Value in (select ValueList from TableA a where a.ID = b.ID)
select * from TableB b where exists ( select 1 from TableA a where a.ID = b.ID and ','+a.ValueList+',' like '%,'+b.Value+',%' )
Notes on design and performance: This design prevents any index being used on the column
ValueList. This may not be a problem if:
TableA is very small and has very few rows (e.g. < 10 rows). This is because if the data fits into one or two pages, the overhead involved with looking up the index may be greater than the overhead involved in just scanning the page and doing string comparisons.
Or only a very small subset of rows are actually being searched.
For example, if you are looking up individual rows by a unique key, or a few tens of rows by an efficient index, and just want to filter based on whether a string is in
ValueList, this may be faster than a junction table, because the data is held in the same page.
In other words, if you are not searching by values from this list, but merely filtering by them, it may not be worth putting them in to a junction table.
As always one should not be dogmatic about design, but test.