mysql - 字符串根据字典替换
问题描述
源表:
字典表:
期望将源表的数值根据字典表进行替换,目标结果如下:
尝试过用replace函数,但是只能单个替换.(注:其他由于是逗号分隔,字典表id可1~100,需要用正则表达式替换,否则12会被id1,2拆开)
问题解答
回答1:自己写的递归函数
DELIMITER $$DROP FUNCTION IF EXISTS junk.StringReplaceSplit $$CREATE FUNCTION junk.StringReplaceSplit (v_str VARCHAR(1024), v_split VARCHAR(1024)) RETURNS VARCHAR(1024)DETERMINISTICBEGIN 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)
函数有待优化.
相关文章:
1. nginx英文文档的WebSocket proxying部分没看太明白,麻烦推荐一点中文文章2. ueditor上传服务器提示后端配置项没有正常加载,求助!!!!!3. python - 关于matplotlib的x轴显示的问题4. angular.js - 怎样在使用ng-repeat属性的标签里面监听单个事件,使其能够单个改变CSS。5. angular.js - angularjs 怎么封装 upload 上传6. 请教: 关于 python 反斜杠转义的疑问7. 绝对定位和fied定位,键盘弹起对布局的影响8. android 文件File删除问题9. javascript - vue生成一维码?求助!!!!!急10. python - Scrapy ItemLoader数据清洗疑问
