mysql - String replacement based on dictionary-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

mysql - String replacement based on dictionary

PHPz 2017-05-18 10:50:38

908

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)

PHPz

学习是最好的投资！

reply all (1)

给我你的怀抱2017-05-18 10:52:38 1 floor

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.