Summing a comma separated column in MySQL 4 (not 5)

by

To do this kind of non trivial string manipulations, you need to use stored procedures, which, for MySQL, only appeared 6 years ago, in version 5.0.

MySQL 4 is now very old, the latest version from branch 4.1 was 4.1.25, in 2008. It is not supported anymore. Most Linux distributions don’t provide it anymore. It’s really time to upgrade.

Here is a solution that works for MySQL 5.0+:

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:

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

More Related Contents:

  1. Using ALTER to drop a column if it exists in MySQL
  2. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  3. 1052: Column ‘id’ in field list is ambiguous
  4. Count the occurrences of DISTINCT values
  5. Codeigniter – multiple database connections
  6. Column count doesn’t match value count at row 1
  7. How to add levenshtein function in mysql?
  8. How to search JSON data in MySQL?
  9. MySQL > Table doesn’t exist. But it does (or it should)
  10. Is the primary key automatically indexed in MySQL?
  11. How do I modify a MySQL column to allow NULL?
  12. Connect to mysql in a docker container from the host
  13. Is there ANY_VALUE capability for mysql 5.6?
  14. Cumulative sum over a set of rows in mysql
  15. Set NOW() as Default Value for datetime datatype?
  16. MySQL update CASE WHEN/THEN/ELSE [duplicate]
  17. No connection could be made because the target machine actively refused it (PHP / WAMP)
  18. MySQL SELECT DISTINCT multiple columns
  19. SQL DELETE with JOIN another table for WHERE condition
  20. How to connect to a MySQL Data Source in Visual Studio
  21. DOs and DONTs for Indexes [closed]
  22. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  23. How can I implement single table inheritance using Laravel’s Eloquent?
  24. Converting SELECT DISTINCT ON queries from Postgresql to MySQL
  25. How to insert images from database?
  26. MySQL – ORDER BY values within IN()
  27. How to change the mysql root password
  28. How can I set autoincrement format to 0001 in MySQL?
  29. Why MYSQL IN keyword not considering NULL values
  30. Returning records from the last 3 months only in MySQL

Leave a Comment