Count values from comma-separated field in MySQL?

You can count values from comma-separated field using CHAR_LENGTH() method from MySQL. The syntax is as follows −

SELECT *, (CHAR_LENGTH(yourColumnName) - CHAR_LENGTH(REPLACE(yourColumnName, ',','')) + 1) as anyVariableName from yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table CountValuesCommaSeparated    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> CommaSeparatedValue text,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.76 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue) values('101,104,1900,46675,7895'); Query OK, 1 row affected (0.16 sec) mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue) values('1010,18949,37465'); Query OK, 1 row affected (0.21 sec) mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue) values('2010,1201,2743874,7485'); Query OK, 1 row affected (0.42 sec) mysql> insert into CountValuesCommaSeparated(CommaSeparatedValue) values('4757,457587,48586,378575,3874765,487565'); Query OK, 1 row affected (0.26 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from CountValuesCommaSeparated;

The following is the output −

+----+-----------------------------------------+ | Id | CommaSeparatedValue                     | +----+-----------------------------------------+ | 1  | 101,104,1900,46675,7895                 | | 2  | 1010,18949,37465                        | | 3  | 2010,1201,2743874,7485                  | | 4  | 4757,457587,48586,378575,3874765,487565 | +----+-----------------------------------------+ 4 rows in set (0.00 sec)

Here is the query to count values from comma separated fields:

mysql> select *,    -> (CHAR_LENGTH(CommaSeparatedValue) - CHAR_LENGTH(REPLACE(CommaSeparatedValue, ',', '')) + 1) as TotalValue    -> from CountValuesCommaSeparated;

The following is the output −

+----+-----------------------------------------+------------+ | Id | CommaSeparatedValue                     | TotalValue | +----+-----------------------------------------+------------+ |  1 | 101,104,1900,46675,7895                 |          5 | |  2 | 1010,18949,37465                        |          3 | |  3 | 2010,1201,2743874,7485                  |          4 | |  4 | 4757,457587,48586,378575,3874765,487565 |          6 | +----+-----------------------------------------+------------+ 4 rows in set (0.00 sec)