由于工作需要需要处理一些以逗号分隔的字符串,每次都要现做很是麻烦,网上找了很多都没有现成的,好吧,自己动手写一个好了
1 CREATE DEFINER = `harri`@`%` FUNCTION `str_for_substr`(`num` int, `str` varchar(5000))2 RETURNS varchar(100)3 BEGIN
4 /*函数功能: 把带逗号的字符串分割取出5 参数: num 要取出的字符串的索引值, 以0开始6 str 以逗号分割的字符串7 扩展: 将逗号替换成其他符合,即可完成不同分隔符拆分字符串,亦可以把分隔符作为参数8 */
9 SET @str_for_substr =10
11 SUBSTRING(12 SUBSTRING_INDEX(str, ',', num + 1),13 CASEnum14 WHEN 0 THEN
15 CHAR_LENGTH(16 SUBSTRING_INDEX(str, ',', num)17 ) + 1
18 ELSE
19 CHAR_LENGTH(20 SUBSTRING_INDEX(str, ',', num)21 ) + 2
22 END,23 CASEnum24 WHEN 0 THEN
25 CHAR_LENGTH(26 SUBSTRING_INDEX(str, ',', num + 1)27 ) -CHAR_LENGTH(28 SUBSTRING_INDEX(str, ',', num)29 )30 ELSE
31 CHAR_LENGTH(32 SUBSTRING_INDEX(str, ',', num + 1)33 ) -CHAR_LENGTH(34 SUBSTRING_INDEX(str, ',', num)35 ) - 1
36 END
37 );38
39
40 RETURN @str_for_substr;41 END;
测试:
mysql> SELECT str_for_substr(3,'one,two,three,four,five,six,seven') as 'value';
+-------+
| value |
+-------+
| four |
+-------+
1 row in set
实例应用
mysql> select * from test;
+---+------------------------+
| a | b |
+---+------------------------+
| 1 | 你好,哈哈,不错 |
| 2 | 测试,不错 |
| 3 | test,test2,test3,test4 |
| 4 | 你好,哈哈,不错 |
| 5 | 你好,哈哈,不错 |
| 6 | 你好,哈哈,不错 |
| 7 | 你好,哈哈,不错 |
| 8 | 你好,哈哈,不错 |
+---+------------------------+
8 rows in set
创建存储过程如下
CREATE DEFINER = `root`@`%` PROCEDURE`split_str`()
SQL SECURITY INVOKERBEGIN
DECLARE a1 varCHAR(20);DECLARE b1 varchar(10000);DECLARE done INT DEFAULTFALSE;DECLARE cur CURSOR FOR SELECT a,b fromtest ;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;OPENcur;
read_loop: LOOPFETCH cur INTOa1,b1;IF done THENLEAVE read_loop;END IF;SET @num = LENGTH(b1) - LENGTH(REPLACE(b1, ',', ''));SET @i = 0;WHILE (@i <=@num) DOINSERT INTOtest1VALUES(
a1,
str_for_substr(@i,b1)
);set @i = @i+1;END WHILE;ENDLOOP;CLOSEcur;END;
执行结果如下
mysql> select * from test1;
+---+-------+
| a | b |
+---+-------+
| 1 | 你好 |
| 1 | 哈哈 |
| 1 | 不错 |
| 2 | 测试 |
| 2 | 不错 |
| 3 | test |
| 3 | test2 |
| 3 | test3 |
| 3 | test4 |
| 4 | 你好 |
| 4 | 哈哈 |
| 4 | 不错 |
| 5 | 你好 |
| 5 | 哈哈 |
| 5 | 不错 |
| 6 | 你好 |
| 6 | 哈哈 |
| 6 | 不错 |
| 7 | 你好 |
| 7 | 哈哈 |
| 7 | 不错 |
| 8 | 你好 |
| 8 | 哈哈 |
| 8 | 不错 |
+---+-------+
24 rows in set