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'
Thanks for your help in advance!