CODEWITHSUNDEEP
sqlsql-servert-sqlcsv
techco1
techco1

Reputation: 61

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 fields 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.

Upvotes: 1

Views: 1124

Answers (3)

Gopal Sanodiya
Gopal Sanodiya

Reputation: 204

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

Upvotes: 0

David
David

Reputation: 218798

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...

Upvotes: 2

gbn
gbn

Reputation: 432180

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

Upvotes: 8

Related Questions