How to Sum Comma Separated Numbers in a MySQL 4 Column?

Summing Comma Separated Column in MySQL 4

A user has encountered a situation where data in a source column can be presented as comma-separated lists of numbers. The objective is to transform this data into a destination column where the numbers are summed. This challenge arises when using MySQL version 4, which lacks the capabilities introduced in MySQL 5.0.

Solution

Unfortunately, MySQL 4 does not support stored procedures, which are essential for intricate string manipulation tasks like the one presented. Therefore, upgrading to MySQL 5.0 or later is the recommended solution.

MySQL 5.0 Solution

For MySQL version 5.0 and above, the following solution can be employed:

DELIMITER //
CREATE FUNCTION SUM_OF_LIST(s TEXT)
  RETURNS DOUBLE
  DETERMINISTIC
  NO SQL
BEGIN
  DECLARE res DOUBLE DEFAULT 0;
  WHILE INSTR(s, ",") > 0 DO
    SET res = res + SUBSTRING_INDEX(s, ",", 1);
    SET s = MID(s, INSTR(s, ",") + 1);
  END WHILE;
  RETURN res + s;
END //
DELIMITER ;

Example Usage:

mysql> SELECT SUM_OF_LIST("5,2.1") AS Result;
+--------+
| Result |
+--------+
|    7.1 |
+--------+

The above is the detailed content of How to Sum Comma Separated Numbers in a MySQL 4 Column?. For more information, please follow other related articles on the PHP Chinese website!