Mysql select serialize data

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',1),':',-1) AS fieldname1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',2),':',-1) AS fieldvalue1,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',3),':',-1) AS fieldname2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',4),':',-1) AS fieldvalue2,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',5),':',-1) AS fieldname3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',6),':',-1) AS fieldvalue3,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',7),':',-1) AS fieldname4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',8),':',-1) AS fieldvalue4,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',9),':',-1) AS fieldname5,
SUBSTRING_INDEX(SUBSTRING_INDEX(old_data,';',10),':',-1) AS fieldvalue5
FROM table;

//##remove double quote 
 REPLACE( SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'gender' AND user_id = wp_users.`ID` LIMIT 1 ),';',2),':',-1),'"','') AS fieldname2

// ###### Another sample

SELECT wp_users.`ID`,
       wp_users.`user_login` AS ic,
       wp_users.`user_email`,
			(SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'first_name' AND user_id = wp_users.`ID` LIMIT 1 ) AS nama_penuh,
            (SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'gender' AND user_id = wp_users.`ID` LIMIT 1 ) AS jantina,
            SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'gender' AND user_id = wp_users.`ID` LIMIT 1 ),';',2),':',-1) AS fieldname1,
            REPLACE( SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_usermeta` WHERE meta_key = 'gender' AND user_id = wp_users.`ID` LIMIT 1 ),';',2),':',-1),'"','') AS fieldname2

FROM wp_users WHERE user_nicename != '0'





//example 3

SELECT 
posts_mengaji.ID AS mengaji_ID, 
posts_mengaji.post_date AS mengaji_post_date, 
posts_mengaji.post_title AS mengaji_post_title, 
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_email' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_email, 
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_tel' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_tel, 
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_alamat' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_alamat, 
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_penjaga' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_penjaga, 
SUBSTRING_INDEX(
  SUBSTRING_INDEX( (SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1 ), '";}' , 1 ),
'"',-1) AS test,

concat('aaaa','\n',(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1 )) AS mengaji_senarai_anak 

FROM wp_posts AS posts_mengaji WHERE posts_mengaji.post_type = 'mengaji' AND posts_mengaji.post_status = 'publish'



//best example
SELECT 
posts_mengaji.ID AS mengaji_ID, 
posts_mengaji.post_date AS mengaji_post_date, 
posts_mengaji.post_title AS mengaji_post_title, 
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_email' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_email, 
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_tel' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_tel, 
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_alamat' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_alamat, 
(SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_penjaga' AND post_id = posts_mengaji.ID LIMIT 1 ) AS mengaji_penjaga, 
concat(
    
'Nama Anak:', 
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1),';',3),':',-1)  , '"', '' ) , 
'\n','Tahap Mengaji:', 
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1),';',5),':',-1)  , '"', '' ) , 
'\n','Nama Guru:', 
REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT meta_value FROM `wp_postmeta` WHERE meta_key = 'mengaji_senarai_anak' AND post_id = posts_mengaji.ID LIMIT 1),';',7),':',-1)  , '"', '' ) 
       
) AS mengaji_senarai_anak




FROM wp_posts AS posts_mengaji WHERE posts_mengaji.post_type = 'mengaji' AND posts_mengaji.post_status = 'publish'