MySQL: Split column by delimiter & replace numbers with values from another column

Question

Using MySQL, split column by delimiter & replace numbers with values from another column.

I have a column named 'path' having paths like '/426/427/428'. I would like to replace category numbers with category names. Result would be like '/Computers/Other accessories/Laser printers'.

This mysql query seems to be a good start, but don't know how to take column values from 'categories' table, 'path' column.

select distinct
 SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', numbers.n), '/', -1) name
from
 (select @rownum := @rownum + 1 as n
 from categories
 cross join (select @rownum := 0) r
 ) numbers 
where n > 0
order by
 n

Now this query splits the string correctly to:

426
427
428

Next would be to have a result like:

id name
426 Computers
427 Other accessories
428 Laser printers

Finally should merge the 'name' column to

'/Computers/Other accessories/Laser printers' 

string.

Thanks for your help in advance!

Answer 1

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