0

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
  • There is 2 ways. You may extract numbers in a query using synthetic numbers table. You may create function which calculates the sum. I'd recommend the second way... PS. Is the numbers ALWAYS wrapped with double-quotes? – Akina Oct 26 '18 at 4:54
  • yes. its always wrapped in double quotes because its json output. – Irfi Oct 26 '18 at 8:19
1
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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.