Split a pattern string into columns in select query

Posted on

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

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?


Answer :

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

Your column is quite like JSON, so perhaps:

  json_value(j,'$.name') name,
  json_value(j,'$.age') age,
  json_value(j,'$.sex') sex
  from (select concat('{',
                      '}') 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).

Leave a Reply

Your email address will not be published. Required fields are marked *