Mysql function Split function realizes _mysql

Set first: Set GLOBAL log_bin_trust_function_creators = 1;

1. Function Func_splitstringtotal: Split the string in the specified way and calculate the total number of cells

Copy Code code as follows:

DELIMITER $$
CREATE 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;

2. Function func_splitstring: Splits the string in the specified way, getting the number of the specified position

Copy Code code as follows:

DELIMITER $$
DROP function IF EXISTS ' func_splitstring ' $$
CREATE 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$$
SELECT func_splitstring (' 1,2,3,4,5,6,7 ', ', ', ', 1);

3. Process splitstring The string and puts it in the temporary table Tmp_split

Copy Code code as follows:

DELIMITER $$
DROP PROCEDURE IF EXISTS ' splitstring ' $$
CREATE PROC Edure ' splitstring '
(in f_string varchar (1000), in F_delimiter varchar (5))

BEGIN
Declare cnt int de Fault 0;
Declare i int default 0;
Set cnt = Func_split_totallength (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_split (f_string,f_delimiter,i));
End While;
end$$
Call splitstring (' a,s,d,f,g,h,j ', ', ', ');
SELECT * from Tmp_split;