How to Count Items in a Comma-Separated List in MySQL?

Counting Items in Comma-Separated Lists in MySQL

Determining the number of items in a comma-separated list stored in a MySQL column can be a straightforward task using a simple formula that leverages string manipulation functions.

To count the items in a column named fooCommaDelimColumn, you can use the following SQL expression:

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

This expression calculates the difference between the original string and the same string with all commas removed. The result gives you the count of items in the list.

For example, if fooCommaDelimColumn contains the value "cats,dogs,cows,," (with an extra trailing comma), the expression would return 4. This is because the original string is 15 characters long, while the comma-less string would be 11 characters long, resulting in a difference of 4.

Note that while this formula works for fields with extra trailing commas, it's important to adjust the calculation accordingly if the data may not have such trailing commas. In those cases, you would add 1 to the result to account for the potentially missing extra comma.

The above is the detailed content of How to Count Items in a Comma-Separated List in MySQL?. For more information, please follow other related articles on the PHP Chinese website!