mysql string count & split

str_count

DROP FUNCTION IF EXISTS str_count;

CREATE FUNCTION str_count(src_str VARCHAR(1000),f_delimiter varchar(50) )

RETURNS int(11)

BEGIN

if length(f_delimiter)=2 then

return 1+( length(src_str)-length(replace(src_str,f_delimiter,'')) )/2;

else

return 1+(length(src_str)-length(replace(src_str,f_delimiter,'')) );

end if;

END;

# SELECT str_count('china,usa,england,russia', ',')  as tmp;

 

str_split

DROP FUNCTION IF EXISTS str_split;

CREATE FUNCTION str_split(

in_str varchar(1000),special_str varchar(5),str_order int) RETURNS varchar(255) CHARSET utf8

BEGIN

declare result varchar(255) default '';

set result = reverse(substring_index(reverse(substring_index(in_str,special_str,str_order)),special_str,1));

return result;

END;


# SELECT str_split('4,440000,440600,440605',',',2) as tmp;