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

吾爱主题阅读：157 2024-04-01 23:50:46 评论：0

1：定义存储过程，用于分隔字符串

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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函数：该函数用于计算分隔之后的长度，这里需要了解的函数：

1 2 3 4 5 6 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'

具体实现：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 （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'

具体实现：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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 ;