oracle的split函数

PL/SQL 中没有split函数，需要自己写。下面的函数，默认按照逗号“，”分割字符串，分割后，从数据库表格中可以取得字符数组的每一个值。

create or replace type type_split as table of varchar2(3000);
create or replace function split_customized(p_list varchar2,p_sep varchar2 := ',')  
return type_split pipelined
is
   l_idx  pls_integer;
   v_list varchar2(3000) := p_list;
begin
   loop
      l_idx := instr(v_list,p_sep);
      if l_idx > 0 then
          pipe row(substr(v_list,1,l_idx-1));
          v_list := substr(v_list,l_idx+length(p_sep));
      else
          pipe row(v_list);
          exit;
      end if;
   end loop;
   return;
end split_customized;

测试：

select COLUMN_VALUE from table(split_customized('Book:Script:Company',':'));

输出：

COLUMN_VALUE
--------------------------------------------------------------------------------
Book
Script
Company

转载自：

http://hi.baidu.com/gwkztmjwfqbdmxe/item/e303790f137ce8016d9048f6