Help with commas in a field

[baze22]

I am working with a database (not mine) where the values in the field can be some thing lke:

ID | FIELD
----------
1      2,4,6
2      23,42
3      2
4      32, 33
etc...

I need to be able to grab the rows based on one of the values separated like 2 but not get 23. And I can't assume a comma like '2,' because 2 could be the final number or part of 32,. I'm hoping this is doable in the query so I can avoid having to grab them all and process seperately.

Thanks,

baze

[Dean C]

Are you trying to match a value in the list? I.e. find whether the number 2 exists in one of the lists in your table?

If so use MySQL's FIND_IN_SET function like so:

SELECT *
FROM table
WHERE FIND_IN_SET(2, fieldname)

[vgarcia]

Fix that database. That's a terrible way to store data and it violates some normalization best practices. Your data should look more like this:

ID | FIELD
----------
1      2
1      4
1      6
2      23
2      42
3      2
4      32
4      33

Then you could search the database properly . I know this might be some extra work on your part but it will save you lots of time down the road.

[baze22]

Are you trying to match a value in the list? I.e. find whether the number 2 exists in one of the lists in your table?

If so use MySQL's FIND_IN_SET function like so:

SELECT *
FROM table
WHERE FIND_IN_SET(2, fieldname)

With the data I have, that looks like it will do the trick. Thanks.

vgarcia, the table is a vbulletin table, kinda gotta leave it as is. I suppose for their purposes (the way they use it) it works fine. I'm writing some stuff that that will interface with it.

thanks for your help,

baze