How to find string count using SQL query of a particular id in a column?

Question

For Example

If i have a table name called Vegans and column Name called Fruits:-

ID     Fruits 
1      Orange,Apple
2      Orange,Apple
3      Apple
4      Orange

How to get count string count of a particular 'ID' in the column name Fruits ?

Fix your data layout to have a junction table (one row per id and per fruit. Then use a simple aggregation/where/join to do what you want. — Gordon Linoff, Aug 06 '15 at 11:23
I do recommend to fix your table as Gordon Linoff suggested. To be able to count fruits in a `Fruits` field, you have to split data on comma. See: http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — Maciej Los, Aug 06 '15 at 11:30
Do you want to know how many fruits are there for a id (like id1 = 2, Id2 =2, ID3=1...) or how many IDs are there for a fruit (like orange=3, apple=3)? — kl78, Aug 06 '15 at 11:55
@KI78 Yes,Exactly that's wat i am expecting ( ID1=2,ID2=2,ID3=1,ID4=1) — Arun, Aug 06 '15 at 19:56

score 1 · Accepted Answer · answered Aug 06 '15 at 11:55

A simple way to achieve this is by taking the count of commas that splits the fruits, and add one (in case there is only one fruit)

SELECT ID,char_length(Fruits) - char_length(REPLACE((Fruits), ',', ''))+1 AS FruitCount
    FROM Vegans
    WHERE char_length(Fruits) > 0 AND ID = 1

As many people suggests you should have each entry of fruit in a separate row, with a link field in addition to the ID field. For example like this:

ID, VeganPersonID, Fruit
1, 5, apple
2, 5, peaches
3, 1, apple

1 Answers1