normalizing delimited string, MySQL

Language: Layout:

drop table if exists old_table;
create table old_table (id int, options varchar(255));
insert into old_table(id, options)values
(1, 'A|10|B|20|C|30'),
(2, 'A|Positive|B|Negative');

-- create sequence table
drop table if exists helper_sequence;
create table helper_sequence (i int auto_increment primary key)
    select null as i
    from information_schema.columns c1
    join information_schema.columns c2
    limit 1000;

-- create normalized table
drop table if exists normalized_table;
create table normalized_table
    select t.id
        , substring_index(substring_index(t.options, '|', i  ), '|', -1) as k
        , substring_index(substring_index(t.options, '|', i+1), '|', -1) as v
    from old_table t
    join helper_sequence s
      on s.i <= char_length(t.options) - char_length(replace(t.options, '|', ''))
    where s.i % 2 = 1
;
select * from normalized_table;

-- convert normalized schema to original
select id, group_concat(concat(k, '|', v) order by k separator '|') as options
from normalized_table
group by id;

-- get ',' delimted list of key-value pairs
select id, group_concat(concat(k, '|', v) order by k separator ',') as options
from normalized_table
group by id;

-- update the original table
update old_table o
join (
    select id, group_concat(concat(k, '|', v) order by k separator ',') as options
    from normalized_table
    group by id
) n using (id)
set o.options = n.options;

select * from old_table;

-- try this with your schema
select *
from normalized_table
where k = 'A';


absolute service time: 1,08 sec