1

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
techco1
  • 61
  • 3
  • 3
    Ugh... why are you storing [serialized values in a database](http://stackoverflow.com/questions/7364803/storing-arrays-in-the-database/7364834#7364834)? – NullUserException Sep 11 '11 at 18:38
  • User gbn has given you the correct answer below. After you try it and see that it works correctly, don't forget to click the check mark after his answer to mark it accepted. – Larry Lustig Sep 11 '11 at 18:52

3 Answers3

8

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

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • @piotr, yes it will - thats why he wraps the field with ','s, to allow the search on ,1, – Derek Sep 11 '11 at 18:35
  • @Piotr: Why not? He's adding the needed terminal delimiters in the expression. – Larry Lustig Sep 11 '11 at 18:36
  • @Piotr Auguscik: why not? If you add a leading and trailing comma (like I have) it will match because `',1,2,3,' LIKE '%,1,%'` is true – gbn Sep 11 '11 at 18:36
  • Hi, That wont work. As some values will be stored just like ... 1. Instead of 2,1,3 etc. That wont also "1" in find 1,2,3. – techco1 Sep 11 '11 at 18:36
  • To echo: this is a bad database design. If the design of the database is under your control the sooner you fixer the less programming pain you will suffer in the future. – Larry Lustig Sep 11 '11 at 18:37
  • @techco1: try it. It **will** work. Can you not read the comments above? – gbn Sep 11 '11 at 18:38
  • @Piotr, he's not comparing 1,2,3 he's comparing ,1,2,3, which will match fine. That's the correct solution for matching delimited string values in SQL, although a correct database structure is faster and safer. – Larry Lustig Sep 11 '11 at 18:39
2
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...

David
  • 208,112
  • 36
  • 198
  • 279
  • Good point. As the fields are stored in a Comma delimited field the only other thing I could do is create a TRIGGER on update/insert. Would you advise this? My trigger knowledge is not great. – techco1 Sep 11 '11 at 18:45
  • 1
    @techco1: I don't know enough about your data to make a specific recommendation. But I don't see how a trigger is going to fix your table structure. Based solely on the data in the question, I would make two tables. One stores the ID and the other stores a foreign key to the ID and also stores a Value. The latter table would have a many-to-one relation to the former table. Same data you have here, but using a relational structure to store it more effectively. – David Sep 11 '11 at 18:48
0

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

Gopal Sanodiya
  • 204
  • 2
  • 3