MySQL – Remove Characters and Sum Numeric Values

MySQLmysql-5.5mysql-5.6

I am using mysql 5.6 and want to remove all characters from json output of db column and after that required sum of numeric values

Input:

{"across_back":"0","bustd":"0","waiscvvt":"50","hisdp":"50","neckdf":"0","thiddgh":"0"}
{"shoulder":"0","bustfd":"10","waisdsdt":"40","sdhip":"10","nsdfeck":"0","thigfsdh":"0"}
{"back":"0","bust":"40","waist":"35","sdfhip":"22","necsdk":"10","thigh":"0"}

Output:

0+0+0+50+0+0 = 50
0+10+40+10+0+0 = 60
0+40+35+22+10+0 107

Best Answer

drop table if exists test;
create table if not exists test(val varchar(255));

drop table if exists log;
create table log(id int auto_increment primary key,
                 val varchar(255),
                 summ int );

insert into test
values
('{"across_back":"0","bustd":"0","waiscvvt":"50","hisdp":"50","neckdf":"0","thiddgh":"0"} '),
('{"shoulder":"0","bustfd":"10","waisdsdt":"40","sdhip":"10","nsdfeck":"0","thigfsdh":"0"} '),
('{"back":"0","bust":"40","waist":"35","sdfhip":"22","necsdk":"10","thigh":"0"}');

drop function if exists my_sum;

DELIMITER @@;

create function my_sum(s text)
returns bigint deterministic
begin
set @sum := 0;
while locate('"', s) do
-- debug 1, remove after debugging
    insert into log(val, summ) select s, @sum;
    set @sum := @sum + substring_index(substring_index(s, '"', 4), '"', -1);
-- debug 2, remove after debugging
    insert into log(val, summ) select s, @sum;
    set s = substring(s FROM 2 + length(substring_index(s, '"', 4)));
-- debug 3, remove after debugging
    insert into log(val, summ) select s, @sum;
end while;
return @sum;
end;
@@;

DELIMITER ;

select val, my_sum(val) from test;

drop table if exists test;
drop function if exists my_sum;

select * from log order by id;
drop table if exists log;
Related Question