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;