How to count items in comma separated list MySQL

by

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

More Related Contents:

  1. Which DB technology is sued to solve the following search issue? [closed]
  2. MySQL pivot row into dynamic number of columns
  3. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  4. Count the number of occurrences of a string in a VARCHAR field?
  5. MySQL – Operand should contain 1 column(s)
  6. How to get the difference in years from two different dates?
  7. Restore table structure from frm and ibd files
  8. mysql pivot/crosstab query
  9. MySQL: Split comma separated list into multiple rows
  10. Most efficient way to get table row count
  11. UNION query with codeigniter’s active record pattern
  12. Groupwise maximum
  13. Filter by COUNT(*)?
  14. Error Code: 1406. Data too long for column – MySQL
  15. MySQL order by “best match”
  16. How to select rows that have current day’s timestamp?
  17. MySQL concatenation operator
  18. Is there any reason to worry about the column order in a table?
  19. SQL Group By with an Order By
  20. Checking a table for time overlap?
  21. Does mysql have the equivalent of Oracle’s “analytic functions”?
  22. MySql – Way to update portion of a string?
  23. Using reserved words in column names
  24. MySQL INNER JOIN select only one row from second table
  25. Storing IPv6 Addresses in MySQL
  26. Insert/ Update random date in MySQL
  27. Replace null with 0 in MySQL
  28. Get total hours worked in a day mysql
  29. Convert SQL Server query to MySQL [duplicate]
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment