mysql字符串分割函数(行转列)

时间:2022-12-09 15:32:24

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

 CREATE DEFINER = `harri`@`%` FUNCTION `str_for_substr`(`num` int, `str` varchar(5000))
RETURNS varchar(100)
BEGIN
/*函数功能: 把带逗号的字符串分割取出
参数: num 要取出的字符串的索引值, 以0开始
str 以逗号分割的字符串
扩展: 将逗号替换成其他符合,即可完成不同分隔符拆分字符串,亦可以把分隔符作为参数
*/
SET @str_for_substr =

SUBSTRING(
SUBSTRING_INDEX(str, ',', num + 1),
CASE num
WHEN 0 THEN
CHAR_LENGTH(
SUBSTRING_INDEX(str, ',', num)
) + 1
ELSE
CHAR_LENGTH(
SUBSTRING_INDEX(str, ',', num)
) + 2
END,
CASE num
WHEN 0 THEN
CHAR_LENGTH(
SUBSTRING_INDEX(str, ',', num + 1)
) - CHAR_LENGTH(
SUBSTRING_INDEX(str, ',', num)
)
ELSE
CHAR_LENGTH(
SUBSTRING_INDEX(str, ',', num + 1)
) - CHAR_LENGTH(
SUBSTRING_INDEX(str, ',', num)
) - 1
END
); RETURN @str_for_substr;
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