Split a pattern string into columns in select query

Question

I have a person table with below structure.

CREATE TABLE person(id integer, details text);
INSERT INTO person(id,details) VALUES(1, "name:Tahir,age:30,sex:male");

I want to split details column by comma and show results in difference columns. Like, in this example result would be id, name,age, sex

I believe that is possible with aggregate functions and I tried to produce a query like below

SELECT id,
if(person.details like '%name:%',cast(substring_index(substring_index(person.details,'name:',-1),',',1) as unsigned),null) as `name`,
if(person.details like '%age:%',cast(substring_index(substring_index(person.details,'age:',-1),',',1) as unsigned),null) as `age`,
if(person.details like '%sex:%',cast(substring_index(substring_index(person.details,'sex:',-1),',',1) as unsigned),null) as `sex`
FROM person

above query fetches data correctly for age but name and sex gets 0. Could you please figure out what I am missing here?

https://www.db-fiddle.com/f/cqyqb7Vhvs7kKYifFywmkV/6

Answer 1

Does this cover your expected result?

SELECT SUBSTRING_INDEX(name, ':', -1) name,
       SUBSTRING_INDEX(age, ':', -1) age,
       SUBSTRING_INDEX(sex, ':', -1) sex
FROM (SELECT SUBSTRING_INDEX(details, ',', 1) name,
             SUBSTRING_INDEX(SUBSTRING_INDEX(details, ',', 2), ',', -1) age,
             SUBSTRING_INDEX(details, ',', -1) sex
      FROM   person) t;

name  | age | sex 
:---- | :-- | :---
Tahir | 30  | male

dbfiddle here

Answer 2

Your column is quite like JSON, so perhaps:

select
  json_value(j,'$.name') name,
  json_value(j,'$.age') age,
  json_value(j,'$.sex') sex
  from (select concat('{',
                      regexp_replace(details,
                                     '([a-z]+):([a-z0-9]+)(,?)',
                                     '"\\1":"\\2"\\3'),
                      '}') J from person) p;

I've run this on an up to date MariaDB, but MySQL wants nothing to do with it (no regexp_replace).