Mysql comma issue

Hi mates,

Please can someone help me?
I need a mysql function to count value separate by commas.

For example:
In my database I have a field with:
1421,24,4785,1487

I want to count that, and give me the result of (4) in that case.

P.S. Sorry for my poor english...

 

Use substr($string,0,1)==',' and loop through the string. Use strlen() to get length of string.

 

What you should do, is normalize your database and create a new table to contain the csv values you're storing in a field right now. One row for each oldtablekey-oldtablecsvfieldvalue.

For example:

oldtablekey   oldtablecsvfield
1             1421,24,4785,1487

newtablekey   newtablecsvfieldvalue
1             1421
1             24
1             4785
1             1487

Code (markup):

That way, querying the DB becomes much easier.

 

Divvy,

This is not a good design unless you have a good reason to do this. Most of the people (even I) do such things to enhace the performance but at the same way I also save the values as CreativeClans suggested. In this way you have more control when creating relations. I save comma separated values to quickly access the sub categories or stuff like that.

If you are not willing to change your database structure, you can simply use MySQL's REPLACE() and LENGHT() functions to count the words. If you run this query on a huge table, this will not be much quicker.
http://www.mwasif.com/2008/12/count-number-of-words-in-a-mysql-column/