MYSQL: Stored function to replace category ids with names I have a stored function which should replace category ids in column path with names from name column. Then store result string into a new column named path_long.

I use Debian 8, MySQL v5.5.

Example

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

I have this stored function:

CREATE DEFINER=`root`@`%` FUNCTION `decode_path`(
    `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''

BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS 
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM @path_input)), '/', 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
);

INSERT INTO t2
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;

RETURN (SELECT path_long FROM t2 limit 1);

END


Here is the test DDL:

CREATE TABLE `category` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `path` VARCHAR(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=429
;


Also test data:

INSERT INTO `category` (`id`, `name`, `path`) VALUES (1, 'A', '/1');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (2, 'B', '/1/2');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (3, 'C', '/1/2/3');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (4, 'D', '/4');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (5, 'E', '/4/5');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (6, 'F', '/4/5/6');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (7, 'G', '/7');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (8, 'H', '/7/8');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (9, 'I', '/7/8/9');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (426, 'Computers', '/426');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (427, 'Other accessories', '/426/427');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (428, 'Laser printers', '/426/427/428');


Unfortunately I can't change the design. It is given in the software. Horrible schema or not, horrible database or not, this is what I have. The framework uses this schema, and the database is MySQL. I have to do the query on this system and I have to get the desired result.

Using query:

SELECT decode_path(category.path) as decoded FROM category


Problem

The query results the following:

decoded 
A
A
A
A
A
A
A
A
A
A
A
A


Source column looks like this (showing undecoded paths):

path
/426/427/428
/1/2/3
/4/5/6
/7/8/9


Desired result column should be like this (showing decoded paths):

path_long
/Computers/Other accessories/Laser printers
/A/B/C
/D/E/F
/G/H/I


Basically it should decode a path having category ids to readable path format using category names. 

How to fix the stored function to make it work?
Does this work? I wrote this up in a hurry on MariaDB so may not be 100% correct, but hoping it should work on MySQL 5.5 as well, or at least only require minor tweaks. It seems to work on your test data.

DELIMITER //
CREATE FUNCTION decode_path (
    `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
  BEGIN
    DECLARE cnt INT UNSIGNED DEFAULT 2;
    DECLARE cat_id INT UNSIGNED;
    DECLARE cat_name VARCHAR(200) DEFAULT '';
    DECLARE decoded_path MEDIUMTEXT;
    SET decoded_path = '';

    WHILE cnt &lt; LENGTH(path_input) - LENGTH(REPLACE(path_input, '/', '')) + 2 DO
      SET cat_id = SUBSTRING_INDEX(SUBSTRING_INDEX(path_input,'/',cnt), '/', -1);
      SELECT `name` INTO cat_name FROM category WHERE id = cat_id;
      SET decoded_path := CONCAT(decoded_path, '/', cat_name);
      SET cnt := cnt + 1;
    END WHILE;
    RETURN decoded_path;
  END
//
DELIMITER ;


Using this, I get:

SELECT path, `name`, decode_path(path) FROM category;
+--------------+-------------------+---------------------------------------------+
| path         | name              | decode_path(path)                           |
+--------------+-------------------+---------------------------------------------+
| /1           | A                 | /A                                          |
| /1/2         | B                 | /A/B                                        |
| /1/2/3       | C                 | /A/B/C                                      |
| /4           | D                 | /D                                          |
| /4/5         | E                 | /D/E                                        |
| /4/5/6       | F                 | /D/E/F                                      |
| /7           | G                 | /G                                          |
| /7/8         | H                 | /G/H                                        |
| /7/8/9       | I                 | /G/H/I                                      |
| /426         | Computers         | /Computers                                  |
| /426/427     | Other accessories | /Computers/Other accessories                |
| /426/427/428 | Laser printers    | /Computers/Other accessories/Laser printers |
+--------------+-------------------+---------------------------------------------+


So to store the result string from the function into a new column named path_long:

UPDATE category SET path_long = decode_path(path); 


Tested with MySQL 5.5 on db-fiddle.com here.