mysql 字典替换_字符串根据字典替换

自己写的递归函数

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)

函数有待优化.