字符串分割处理函数

description:according to the delimited to split the string.

create or replace type t_vc is table of varchar2(100);
/

create or replace function myconvert(p_str varchar2, p_delim varchar2)
  return t_vc as
  v_result   t_vc;
  v_delimlen number := length(p_delim);
  i          number := 1;
  j          number;
  p          integer; --pointer to string
  p2         integer; --pointer to delim
begin
  if p_str is null then
    return null;
  elsif p_delim is null then
    return t_vc(p_str);
  end if;

  v_result := t_vc();
  p        := 1;
  loop
    p2 := instr(p_str, p_delim, p);
    v_result.extend;
    if p2 <> 0 then
      v_result(i) := substr(p_str, p, p2 - p);
    else
      v_result(i) := substr(p_str, p);
      exit;
    end if;
 
    -- check duplication value
    for j in 1 .. i loop
      if j <> i and v_result(j) = v_result(i) then
        raise_application_error(-20000,
                                'Duplicate value on ' || j || ' and ' || i || ' :' ||
                                v_result(i));
      end if;
    end loop;
 
    p := p2 + v_delimlen;
    i := i + 1;
  end loop;

  return v_result;
end;
/

SELECT column_value FROM TABLE(myconvert('junhua zhang,Jane',','));