How to Count Items in Comma-Separated Strings in MySQL?

Counting Items in Comma Separated Strings in MySQL

You need to determine the number of items in a comma-separated column in your MySQL database. To achieve this, you can utilize the following technique:

By calculating the difference between the length of the original string and the length of the string without commas, you can obtain the count of items:

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

This expression takes into account the trailing comma in the string, so you won't need to add an additional 1. For strings that do not have a trailing comma, you can add 1 to the expression:

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

This approach utilizes the built-in LENGTH and REPLACE functions to accomplish the task without utilizing external functions or custom code.

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