0

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!

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.