Mysql function split learning mysql 5. * The version does not have the split function, but it will be used in some places. Here we will simply record it: 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 www.2cto.com Java code DELIMITER $ create function 'func _ get_splitstringtotal' (f_string varchar (10000), f_delimiter varchar )) RETURNS int (11) BEGIN return 1 + (length (f_string)-length (replace (f_string, f_delimiter, ''); END $ DELIMITER; 2. function func_splitString: Splits a string according to the specified method, get the number of Java code www.2cto.com DELIMITER at the specified location $ 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. the splitString is used to split the string and put it into the temporary table tmp_split. Java code DELIMITER $ drop procedure if exists 'splitstring' $ create procedure 'splitstring' (IN f_string varchar (1000 ), IN f_delimiter varchar (5 ))
BEGIN www.2cto.com 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;