How to count items in comma separated list MySQL

Question

So my question is pretty simple:

I have a column in SQL which is a comma separated list (ie cats,dogs,cows,) I need to count the number of items in it using only sql (so whatever my function is (lets call it fx for now) would work like this:

 SELECT fx(fooCommaDelimColumn) AS listCount FROM table WHERE id=... 

I know that that is flawed, but you get the idea (BTW if the value of fooCommaDelimColumn is cats,dogs,cows,, then listCount should return 4...).

That is all.

Answer 1

There is no built-in function that counts occurences of substring in a string, but you can calculate the difference between the original string, and the same string without commas:

LENGTH(fooCommaDelimColumn) - LENGTH(REPLACE(fooCommaDelimColumn, ',', '')) 

It was edited multiple times over the course of almost 8 years now (wow!), so for sake of clarity: the query above does not need a + 1, because OPs data has an extra trailing comma.

While indeed, in general case for the string that looks like this: foo,bar,baz the correct expression would be

LENGTH(col) - LENGTH(REPLACE(col, ',', '')) + 1