mysql - String replacement based on dictionary

Question

Source table:

Dictionary table:

It is expected that the values ​​​​in the source table will be replaced according to the dictionary table. The target result is as follows:

I tried to use the replace function, but it can only replace a single one.
(Note: Since other values ​​are separated by commas, the dictionary table id can be 1~100, so it needs to be replaced with a regular expression, otherwise 12 will be split by id1 and 2. open)

Answer 1

Recursive function written by myself

DELIMITER $$
DROP FUNCTION IF EXISTS junk.StringReplaceSplit $$
CREATE FUNCTION junk.StringReplaceSplit (v_str VARCHAR(1024), v_split VARCHAR(1024)) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
  DECLARE rv VARCHAR(1024);
  DECLARE splist_count INT;
  DECLARE i INT;
  DROP TEMPORARY TABLE IF EXISTS tmp_table;
  CREATE TEMPORARY TABLE tmp_table(v_key VARCHAR(100));
 
  SET i = 1;
  set splist_count = (length(v_str)-length(replace(v_str, v_split,'')))/length(v_split)+1;
  WHILE i <= splist_count DO
    INSERT INTO tmp_table
      SELECT REPLACE(substring(substring_index(v_str, v_split, i),
                     length(substring_index(v_str, v_split, i-1)) + 1),
                      v_split, '');
    SET i = i + 1;
  END WHILE;
  SELECT replace(group_concat(ifnull(wordlist.word, tmp_table.v_key)), ',', v_split) INTO rv
    from tmp_table
    LEFT JOIN wordlist on wordlist.id=tmp_table.v_key;
  RETURN rv;
END $$
DELIMITER ;

Effect:
Source table

mysql> select * from teststring;
+----+----------------------------+
| id | String                     |
+----+----------------------------+
|  1 | 1,2,3,4,5                  |
|  2 | 4,5,7,8,84                 |
|  3 | key,23,344,12,3,9,34,3,1,3 |
+----+----------------------------+
3 rows in set (0.00 sec)

Dictionary table

mysql> select * from wordlist;
+----+-------+
| id | word  |
+----+-------+
| 1  | one   |
| 2  | two   |
| 3  | three |
| 4  | four  |
| 5  | five  |
| 7  | six   |
| 8  | eight |
| 9  | nine  |
| 10 | ten   |
+----+-------+
9 rows in set (0.00 sec)

Example:

mysql> SELECT id, StringReplaceSplit(String,',') transform from teststring ;
+----+---------------------------------------------+
| id | transform                                   |
+----+---------------------------------------------+
|  1 | one,two,three,four,five                     |
|  2 | four,five,six,eight,84                      |
|  3 | key,23,344,12,three,nine,34,three,one,three |
+----+---------------------------------------------+
3 rows in set (0.04 sec)

The function needs to be optimized.