Split a pattern string into columns in select query 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
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