问删除字符&和数字值

EN

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;