Comma Delimited Field in Database

I have a database that has a field which is comma delimited.

Basically I want to be able to search on the fi开发者_如何转开发elds easily.

ID    Value
1     1,2,3
2     2,3,4
3     2,3,1
4     1

For example, how do I query the database and get the database field ID and Value for any values that have "1" against the value. This should return ID's 1,3, and 4.

If you can't fix your design (see Storing multiple choice values in database)

...
WHERE
   ',' + field + ',' LIKE '%,1,%'

Edit: also see Storing arrays in the database for another critique of this design

SELECT ID FROM Table WHERE ',' + Value + ',' LIKE '%,@value,%'

I highly recommend, however, changing your database design. There's no reason to have a delimited list in a relational database. Each value should have it's own distinct place in the data model. (This is especially true if your values in this case are supposed to be numbers, because in this design they're characters.)

Edit: Corrected the query, @gbn noticed something I didn't...

select id from table where charindex(1,value)>0