Summing a comma separated column in MySQL 4 (not 5

Question

I'm writing a query that selects data from one table into another, one of the columns that needs to be moved is a DECIMAL column. For reasons beyond my control, the source column can sometimes be a comma separated list of numbers. Is there an elegant sql only way to do this?

For example:

source column

10.2
5,2.1
4

Should produce a destination column

10.2
7.1
4

I'm using MySQL 4, btw.

Answer 1

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 |
+--------+

Answer 2

Here is a mysql function to split a string:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

And u have to use it this way:

SELECT SPLIT_STR(FIELD, ',', 1) + SPLIT_STR(FIELD, ',', 2)  FROM TABLE

Answer 3

Unfortunately mysql does not include string split functions or aggregates, so you will need to do this either in a stored procedure or on the client side.

Answer 4

A number table-based parse approach can be found at this SQLFiddle link. Esentially, once you have the substrings, the sum function will auto-cast the numbers. For convenience:

create table scores (id int primary key auto_increment, valueset varchar(30));
insert into scores (valueset) values ('7,6,8');
insert into scores (valueset) values ('3,2');

create table numbers (n int primary key auto_increment, stuffer varchar(3));
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);
insert into numbers (stuffer) values (NULL);

SELECT ID, SUM(SCORE) AS SCORE
FROM (
        SELECT
          S.id
          ,SUBSTRING_INDEX(SUBSTRING_INDEX(S.valueset, ',', numbers.n),',',-1) score
          , Numbers.n
        FROM
          numbers
          JOIN scores S ON CHAR_LENGTH(S.valueset)
            -CHAR_LENGTH(REPLACE(S.valueset, ',', ''))>=numbers.n-1
) Z
GROUP BY ID
  ;