字符串根据字典替换

Question

源表:

字典表:

期望将源表的数值根据字典表进行替换,目标结果如下:

尝试过用replace函数,但是只能单个替换.
(注:其他由于是逗号分隔,字典表id可1~100,需要用正则表达式替换,否则12会被id1,2拆开)

Answer 1

自己写的递归函数

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 ;

效果:
源表

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)

字典表

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)

示例:

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)

函数有待优化.