Count and Parse

How to do a count on a field as if it was multiple fields

[cyberax18]

I have a database that contains one field with a list of comma seperated values. I want to be able to do an automatic count operation that tells me how many times each value appears in the entire table. Any ideas how I even begin to go about this. An example table might be.

RECORD_NO ¦ FIELD VALUE
1 ¦ g1,g2,g3,g4,g5
2 ¦ g3,g4,g5,g6
3 ¦ g2,g6

What I'm wanting as an output would be
g1=1, g2=2, g3=2, g4=2, g5=2, g6=2

The main aim of the way the table is set up is to make maintenance easier, rather than having to update the table and code everytime I want to add a new value. I'm using PHP and MySQL if its any help.

[physics]

The way you have your database set up is not really optimal, thus to do what you want is tricky. I think that the best thing would be to change the way you store information in your database I.e.

RECORD_NO ¦ FIELD VALUE
1 ¦ g1
1 ¦ g2
1 ¦ g3
1 ¦ g4
1 ¦ g5
2 ¦ g3
2 ¦ g4
2 ¦ g5
2 ¦ g6
3 ¦ g2
3 ¦ g6

Or if you need to, make a seperate table like that.

Then try the following query:
SELECT FIELD_VALUE,COUNT(*) FROM table GROUP BY FIELD_VALUE HAVING COUNT(*) > 1

[physics]

Also you could split up the values with a regex and use them as keys of a hash, incrementing the value each time one is found with the regex.