Solución:
No hay una función incorporada que cuente las apariciones de subcadenas en una cadena, pero puede calcular la diferencia entre la cadena original y la misma cadena sin comas:
LENGTH(fooCommaDelimColumn) - LENGTH(REPLACE(fooCommaDelimColumn, ',', ''))
Se editó varias veces en el transcurso de casi 8 años (¡guau!), Así que en aras de la claridad: la consulta anterior no necesita un + 1, porque los datos de OP tienen una coma al final adicional.
Si bien, de hecho, en el caso general de la cadena que se ve así: foo,bar,baz la expresión correcta sería
LENGTH(col) - LENGTH(REPLACE(col, ',', '')) + 1
La solución de zerkms funciona, de eso no hay duda. Pero su problema es creado por un esquema de base de datos incorrecto, como señaló Steve Wellens. No debe tener más de un valor en una columna porque infringe la primera ley normal. En su lugar, debes hacer al menos dos tablas. Por ejemplo, digamos que tienes miembros quien posee animales :
table member (member_id, member_name)
table member_animal (member_id, animal_name)
Aún mejor: dado que muchos usuarios pueden tener el mismo tipo de animal, debes crear 3 tablas:
table member (member_id, member_name)
table animal (animal_id, animal_name)
table member_animal (member_id, animal_id)
Podrías llenar tus tablas de esta manera, por ejemplo:
member (1, 'Tomas')
member (2, 'Vincent')
animal (1, 'cat')
animal (2, 'dog')
animal (3, 'turtle')
member_animal (1, 1)
member_animal (1, 3)
member_animal (2, 2)
member_animal (2, 3)
Y, para responder a su pregunta inicial, esto es lo que haría si quisiera saber cuántos animales tiene cada usuario:
SELECT member_id, COUNT(*) AS num_animals
FROM member
INNER JOIN member_animal
USING (member_id)
INNER JOIN animal
USING (animal_id)
GROUP BY member_id;
Siguiendo la sugerencia de @zerkms.
Si no sabe si hay una coma al final o no, use la función TRIM para eliminar las comas al final:
(
LENGTH(TRIM(BOTH ',' FROM fooCommaDelimColumn))
- LENGTH(REPLACE(TRIM(BOTH ',' FROM fooCommaDelimColumn), ',', ''))
+ 1
) as count
Referencia: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim
También estoy de acuerdo en que una refactorización de las tablas es la mejor opción, pero si esto no es posible ahora, este fragmento puede hacer el trabajo.