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.

I think you will need to look at stored procedures for this case. — datasage, Jun 10 '11 at 20:11
Is 2 the max number of values for each field or you can have more? Could you use a scripting language? Mysql 4 is a great limit for your need. — Nicola Cossu, Jun 10 '11 at 20:59

score 10 · Accepted Answer · answered Jun 11 '11 at 09:20

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

Nice solution. It's possible in MySQL run an eval por math purpouses? — Ragen Dazs, Jul 30 '15 at 21:35

score 2 · Answer 2 · answered Jun 10 '11 at 20:29

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

score 0 · Answer 3 · answered Jun 10 '14 at 20:46

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
  ;

score 0 · Answer 4 · answered Jun 10 '11 at 20:13

0

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.

answered Jun 10 '11 at 20:13

Femi

64,273
8
118
148

I thought this was at least somewhat helpful, don't know what the down vote was for... – Parris Varney Jun 10 '11 at 21:08

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

4 Answers4

Linked

Related