How can I sum comma-separated decimal values in a MySQL 4 database column?

Summing a Comma-Separated Column Dilemma in MySQL 4

In a MySQL 4 database, you may encounter a challenge while attempting to transfer data between tables, particularly when one of the columns contains a comma-separated list of decimal values instead of a single digit. Achieving the desired summation of these values within the SQL environment poses difficulties due to its lack of certain capabilities.

In such cases, employing stored procedures proves essential, as these were only introduced with the advent of MySQL 5.0. However, since MySQL 4 is an outdated version without current support or availability in mainstream Linux distributions, an upgrade is strongly recommended.

To address the specific issue at hand, a solution tailored for MySQL 5.0 and above is provided:

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 ;

This function can be utilized through the following example:

SELECT SUM_OF_LIST("5,2.1") AS Result;

The resulting output would be:

+--------+
| Result |
+--------+
|    7.1 |
+--------+

The above is the detailed content of How can I sum comma-separated decimal values in a MySQL 4 database column?. For more information, please follow other related articles on the PHP Chinese website!