实现分割字符串。 delimiter $$ create definer=`root`@`%` function `func_get_split_string_total`( f_string varchar(1000),f_delimiter varchar(5) ) returns int(11) begin -- get the total number of given string. return 1+(length(f_string) - leng
实现分割字符串。delimiter $$ create definer=`root`@`%` function `func_get_split_string_total`( f_string varchar(1000),f_delimiter varchar(5) ) returns int(11) begin -- get the total number of given string. return 1+(length(f_string) - length(replace(f_string,f_delimiter,''))); end$$ delimiter ;delimiter $$ create definer=`root`@`%` function `func_get_split_string`( f_string varchar(1000),f_delimiter varchar(5),f_order int) returns varchar(255) charset utf8 begin -- get the separated number of given string. 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$$ delimiter ;
delimiter $$ create procedure `sp_print_result`( in f_string varchar(1000),in f_delimiter varchar(5) ) begin -- get the separated string. declare cnt int default 0; declare i int default 0; set cnt = func_get_split_string_total(f_string,f_delimiter); drop table if exists tmp_print; create temporary table tmp_print (v_text varchar(200) not null); while i 我们来执行：
call sp_print_result('love,you,hate,number',',');query resultv_text love you hate number
create or replace function split_to_string( in f_string text, in f_delimiter varchar(10) ) returns setof text as $ytt$ declare cnt int; declare i int; declare v_result text; begin i := 1; cnt := length(f_string) - length(replace(f_string,f_delimiter,''))+1; while i <= cnt loop v_result := split_part(f_string,f_delimiter,i); return next v_result; i := i + 1; end loop; end; $ytt$ language plpgsql; 结果： t_girl=# select split_to_string('love,you,hate,number',',') as result; result -------- love you hate number (4 rows)t_girl=# select ytt from regexp_split_to_table('love,you,hate,number', e',+') as ytt; ytt -------- love you hate number (4 rows) t_girl=#
第三种，用自带的with 语法来实现。
t_girl=# with recursive ytt(f1,f2) as (values (0,' '::text) union all select f1+1,split_part('love,you,hate,number',',',f1+1) from ytt where f1 =1 and f1 <= length('love,you,hate,number')-length(replace('love,you,hate,number',',',''))+1; result -------- love you hate number(4 rows)time: 0.742 ms