mysql splitstrr_mysql split函数用逗号分隔的实现

1:定义存储过程,用于分隔字符串

DELIMITER $$

USE `mess`$$

DROP PROCEDURE IF EXISTS `splitString`$$

CREATE DEFINER=`root`@`%` PROCEDURE `splitString`(IN f_string VARCHAR(1000),IN f_delimiter VARCHAR(5))

BEGIN

DECLARE cnt INT DEFAULT 0;

DECLARE i INT DEFAULT 0;

SET cnt = func_get_splitStringTotal(f_string,f_delimiter);

DROP TABLE IF EXISTS `tmp_split`;

CREATE TEMPORARY TABLE `tmp_split` (`val_` VARCHAR(128) NOT NULL) DEFAULT CHARSET=utf8;

WHILE i < cnt

DO

SET i = i + 1;

INSERT INTO tmp_split(`val_`) VALUES (func_splitString(f_string,f_delimiter,i));

END WHILE;

END$$

DELIMITER ;

2:实现func_get_splitStringTotal函数:该函数用于计算分隔之后的长度,这里需要了解的函数:

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

例如:

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');

-> 'WwWwWw.mysql.com'

具体实现:

DELIMITER $$

USE `mess`$$

DROP FUNCTION IF EXISTS `func_get_splitStringTotal`$$

CREATE DEFINER=`root`@`%` FUNCTION `func_get_splitStringTotal`(

f_string VARCHAR(10000),f_delimiter VARCHAR(50)

) RETURNS INT(11)

BEGIN

RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));

END$$

DELIMITER ;

3:实现func_splitString函数:用于获取分隔之后每次循环的值,这里需要了解的函数:

(1)REVERSE(str)

Returns the string str with the order of the characters reversed.

例如:mysql> SELECT REVERSE('abc');

-> 'cba'

(2)

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

例如:

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);

-> 'www.mysql'

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);

-> 'mysql.com'

具体实现:

DELIMITER $$

USE `mess`$$

DROP FUNCTION IF EXISTS `func_splitString`$$

CREATE DEFINER=`root`@`%` FUNCTION `func_splitString`( f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8

BEGIN

DECLARE result VARCHAR(255) DEFAULT '';

SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));

RETURN result;

END$$

DELIMITER ;

使用:

(1)调用存储过程:

CALL splitString('1,3,5,7,9',',');

(2):查看临时表

SELECT val_ FROM tmp_split AS t1;

结果:

23c535f09d94e6cf0bbc41d7903c7c73.png

到此这篇关于mysql split函数用逗号分隔的实现的文章就介绍到这了,更多相关mysql split逗号分隔内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值