MySQL: Split column by delimiter & replace numbers with values from another column
I got the following query:

CREATE TEMPORARY TABLE IF NOT EXISTS 
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp 
    GROUP BY catid
    ORDER BY
       n

);

SELECT * FROM t1;
DROP TEMPORARY TABLE t1;


Result is:

*nr*   *catid*   *name*   *path*  
1   426   Computers   /426  
2   427   Other accessories   /426/427  
3   428   Laser printers   /426/427/428  


This is almost good, just need to merge the name column with delimiters.

So final query is:

CREATE TEMPORARY TABLE IF NOT EXISTS 
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp 
    GROUP BY catid
    ORDER BY
       n

);

/* SELECT * FROM t1; */
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;
DROP TEMPORARY TABLE t1;


We get the following query result:

path_long
/Computers/Other accessories/Laser printers