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. javascript - 按鈕鏈接到另一個網址 怎么通過百度統計計算按鈕的點擊數量2. sql語句 - 如何在mysql中批量添加用戶?3. mysql 可以從 TCP 連接但是不能從 socket 鏈接4. mysql - PHP定時通知、按時發布怎么做?5. 怎么php怎么通過數組顯示sql查詢結果呢,查詢結果有多條,如圖。6. mysql - JAVA怎么實現一個DAO同時實現查詢兩個實體類的結果集7. 事務 - mysql共享鎖lock in share mode的實際使用場景8. mysql建表索引問題求助9. mysql - 數據庫建字段,默認值空和empty string有什么區別 11010. mysql 非主鍵做范圍查找實現原理的一點困惑
