Implementation of mysql split function separated by commas

1: Define a stored procedure to separate strings

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: Implement the func_get_splitStringTotal function: This function is used to calculate the length after separation. The functions you need to know here are:

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.
For example:
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
    -> 'WwWwWw.mysql.com'

Specific implementation:

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: Implement the func_splitString function: used to obtain the value of each loop after separation. The functions you need to know here are:

(1) REVERSE(str)

Returns the string str with the order of the characters reversed.
For example: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.

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

Specific implementation:

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 ;

use:

(1) Calling a stored procedure:

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

(2): View the temporary table

SELECT val_ FROM tmp_split AS t1;

result:

This is the end of this article about the implementation of mysql split function with comma separation. For more relevant mysql split comma separation content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: