How to Sum Comma-Separated Values in a Column in Legacy MySQL Versions (MySQL 4)?

Summing a Comma-Separated Column in MySQL: A Solution for Legacy Versions

In MySQL version 4, handling comma-separated numeric columns presents a challenge when attempting to perform aggregations. However, while modern versions of MySQL offer stored procedures to address such complexities, they are not available in MySQL 4.

Unfortunately, MySQL 4 is an outdated version, and its use is highly discouraged. The latest release in the 4.1 branch was 4.1.25 in 2008, rendering it unsupported and obsolete. Most Linux distributions have discontinued providing MySQL 4. If possible, upgrading to MySQL 5 is strongly recommended.

Nonetheless, if upgrading is not feasible, here is an efficient solution for summing comma-separated decimal columns in MySQL 4:

<code class="mysql">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 ;</code>

Usage Example:

<code class="mysql">mysql> SELECT SUM_OF_LIST("5,2.1") AS Result;
+--------+
| Result |
+--------+
|    7.1 |
+--------+</code>

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