多字符串分割与拼接

create or replace function split_connect(split_char   in varchar2,
                                         insert_char  in varchar2,
                                         insert_char2 in varchar2,
                                         column1      in varchar2,
                                         column2      in varchar2)
  return varchar2 as
  /********************
  select split_connect(',',':',';',column1,column2) from split_table
  大刀,扫帚   2,3  ----> 大刀:2;扫帚:3
  split_char 分隔字符串(例子中的 ‘,')
  insert_char 连接字符串1（:）
  insert_char2 连接字符串2(;)
  column1 字段1 (大刀,扫帚)
  column2 字段2 (2,3)
  *********************/
  result_text  varchar2(1000);
  column1_temp varchar2(1000);
  column2_temp varchar2(1000);
begin
  column1_temp := column1;
  column2_temp := column2;
  while instr(column1_temp, split_char) > 0 loop
    if result_text is null then
      result_text := substr(column1_temp,
                            0,
                            instr(column1_temp, split_char) - 1) ||
                     insert_char ||
                     substr(column2_temp,
                            0,
                            instr(column2_temp, split_char) - 1);
    else
      result_text := result_text || insert_char2 ||
                     substr(column1_temp,
                            0,
                            instr(column1_temp, split_char) - 1) ||
                     insert_char ||
                     substr(column2_temp,
                            0,
                            instr(column2_temp, split_char) - 1);
    end if;
    column1_temp := substr(column1_temp,
                           instr(column1_temp, split_char) + 1,
                           length(column1_temp));
    column2_temp := substr(column2_temp,
                           instr(column2_temp, split_char) + 1,
                           length(column2_temp));
  end loop;
  if column1_temp is not null or length(column1_temp) > 0 then
    if result_text is null or length(result_text) = 0 then
      result_text := column1_temp || insert_char || column2_temp;
    else
      result_text := result_text || insert_char2 || column1_temp ||
                     insert_char || column2_temp;
    end if;
  end if;
  return result_text;
EXCEPTION
  WHEN OTHERS THEN
    RETURN column1_temp || insert_char2 || column2_temp;
end split_connect;