56,677
社区成员
发帖
与我相关
我的任务
分享
DELIMITER $$
USE `test`$$
DROP FUNCTION IF EXISTS `func_get_split_string`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `func_get_split_string`(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 ;
mysql> SELECT func_get_split_string('9001#10018#15#','#',3);
+-----------------------------------------------+
| func_get_split_string('9001#10018#15#','#',3) |
+-----------------------------------------------+
| 15 |
+-----------------------------------------------+
1 row in set (0.02 sec)
DELIMITER $$
USE `test`$$
DROP FUNCTION IF EXISTS `countOrder`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `countOrder`(f_string VARCHAR(1000),f_delimiter VARCHAR(5)) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
DECLARE result VARCHAR(255) DEFAULT '';
DECLARE lastresult VARCHAR(255) DEFAULT '';
DECLARE f_order INT DEFAULT 1;
SET result=func_get_split_string(f_string,f_delimiter,f_order);
WHILE TRIM(result)!='' DO
SET lastresult=CONCAT_WS(' ',lastresult,NULL,result);
SET f_order=f_order+1;
SET result=func_get_split_string(f_string,f_delimiter,f_order);
END WHILE;
RETURN lastresult;
END$$
DELIMITER ;
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
mysql> select SUBSTRING_INDEX(SUBSTRING_INDEX('9001#10018#15#','#',3),'#',-1);
+-----------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('9001#10018#15#','#',3),'#',-1) |
+-----------------------------------------------------------------+
| 15 |
+-----------------------------------------------------------------+
1 row in set (0.36 sec)
mysql>