mysql字符串分割函数（行转列）

由于工作需要需要处理一些以逗号分隔的字符串，每次都要现做很是麻烦，网上找了很多都没有现成的，好吧，自己动手写一个好了

 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         CASE num
14     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     CASE num
24 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 INVOKER
BEGIN


  DECLARE a1 varCHAR(20);
  DECLARE b1 varchar(10000);
  
  DECLARE done INT DEFAULT FALSE;

  
  DECLARE cur CURSOR FOR SELECT a,b from test ;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  OPEN cur;
  
  
  read_loop: LOOP
    
    FETCH cur INTO a1,b1;

    
    IF done THEN
      LEAVE read_loop;
    END IF;

   SET @num = LENGTH(b1) - LENGTH(REPLACE(b1, ',', ''));


SET @i = 0;


WHILE (@i <=@num ) DO
    INSERT INTO test1
VALUES
    (
        a1,
      str_for_substr(@i,b1)
);

set @i = @i+1;

END WHILE;

  END LOOP;
  
  CLOSE cur;


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

 

posted @ 2015-10-16 18:33  小草青青  阅读(30030)  评论(0)  编辑  收藏  举报