Mysql function split function implementation _ MySQL

The mysql function split function is used to implement bitsCN.com. set global log_bin_trust_function_creators = 1;

1. function func_splitStringTotal: splits the string according to the specified method and calculates the total number of units.


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 to obtain the number of specified positions.


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 (f_string, f_delimiter, f_order), f_delimiter, 1 ));
Return result;
END $
SELECT func_splitString ('1, 2, 3, 4, 5, 6, 7 ', 1 );


3. split the strings in the splitString process and put them in the tmp_split temporary table.


DELIMITER $
Drop procedure if exists 'splitstring' $
Create 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_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;
BitsCN.com