How to count id numbers separated by a comma from one cell

Question

I have table with 5 columns and one of it is column called product_id where i store id's spearated by a comma like:

1,23,43

is that possible to count that numbers by mysql query? I need to know it to make pagination of products...

i need to know that there is 3 ids (3 products)

best regards m.

here's a good article: http://stackoverflow.com/questions/7020001/how-to-count-items-in-comma-seperated-list-mysql — John Woo, Sep 16 '13 at 18:28
Did you write this schema, or do you have any influence over its design? You shouldn't be storing [many-to-many](http://en.wikipedia.org/wiki/Many-to-many_(data_model) relationships like that. — Seventoes, Sep 16 '13 at 18:29
i have products stored in DB normal as one product one row BUT there is smth like promotion and I put these products to that promotion ... connect these products with promotion table, row and put id's to one cell with comma separated. So i need to know how many products is on this promotion :) — Kenny, Sep 16 '13 at 18:37

score 1 · Accepted Answer · answered Sep 16 '13 at 18:38

1

try this

select LENGTH(product_id) - LENGTH(REPLACE(product_id, ',', ''))+1 as counts
from table1

answered Sep 16 '13 at 18:38

echo_Me

Thank You, that works :) i made count in other way too, but nice to learn from You :) best regards – Kenny Sep 16 '13 at 19:05
you welcome newbie :) ! – echo_Me Sep 16 '13 at 19:27

1 Answers1