Geek Answers Handbook

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

I am writing a query that selects data from one table to another, one of the columns to be moved is the DECIMAL column. For reasons beyond my control, the source column can sometimes be a comma-separated list of numbers. Is there an elegant sql method just for this?

For instance:

source column

10.2 5,2.1 4

Must form a destination column

 10.2 7.1 4

I am using MySQL 4, btw.

+4
source share
4 answers

To perform such nontrivial string manipulations, you need to use stored procedures, which for MySQL appeared only 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 no longer supported. Most Linux distributions no longer provide. It really is 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 | +--------+
+8
source

Here is the mysql function to split the 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 you need to use it as follows:

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

Unfortunately, mysql does not include line-breaking or aggregation functions, so you will need to do this either in a stored procedure or on the client side.

0
source

On this SQLFiddle, you can find an approach to the analysis based on the number of tables. Typically, after you have substrings, the sum function will automatically produce a number. For comfort:

 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 ;
0
source

More articles:


All Articles