Sum Comma-Separated String with Numbers in MySQL

You can create a custom function to sum a comma-separated string in MySQL. Let us first create a table. Here, we have a varchar column, wherein we will add numbers in the form of strings −

mysql> create table DemoTable
   -> (
   -> ListOfValues varchar(50)
   -> );
Query OK, 0 rows affected (0.56 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('20,10,40,50,60');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----------------+
| ListOfValues   |
+----------------+
| 20,10,40,50,60 |
+----------------+
1 row in set (0.00 sec)

Here is the query to create a function −

mysql> DELIMITER ??
mysql> create function totalSumInCommaSeparatedString(input varchar(50))
   -> returns int
   -> deterministic
   -> no sql
   -> begin
   -> declare totalSum int default 0;
   -> while instr(input, ",") > 0 do
   -> set totalSum = totalSum + substring_index(input, ",", 1);
   -> set input = mid(input, instr(input, ",") + 1);
   -> end while;
   -> return totalSum + input;
   -> end ??
Query OK, 0 rows affected (0.17 sec)
mysql> DELIMITER ;

Let us check the above function to get some of a comma-separated string in MySQL −

mysql> select totalSumInCommaSeparatedString(ListOfValues) as TotalSum from DemoTable;

This will produce the following output −

+----------+
| TotalSum |
+----------+
|      180 |
+----------+
1 row in set (0.00 sec)