oracle 分割字符串，并取对应的值

create or replace function strsplit(p_value varchar2,--字符串

                                    p_split varchar2,--分隔符

                                    p_num integer)--取第几位值

--usage: select * from table(strsplit('1,2,3,4,5'))

 return varchar2 as

  idx       integer;

  str       varchar2(500);

  strs_last varchar2(4000) := p_value;

  num integer:=p_num;

begin

  loop

    idx := instr(strs_last, p_split);

    exit when idx = 0 or num = 0;

    str       := substr(strs_last, 1, idx - 1);

    strs_last := substr(strs_last, idx + 1);

    num := num-1;

  end loop;

  if num = 1 and idx = 0 then str :=strs_last;

    end if;

  return str;

end strsplit;

效果如图：