oracle的split函数

字符分隔函数是很常用的, oracle暂时没有默认实现,需要自己编写.

--创建一个　　type　　，如果为了使split函数具有通用性，请将其size 设大些。
CREATE OR REPLACE type strsplit_type
AS
  TABLE OF VARCHAR2(50);

CREATE OR REPLACE FUNCTION strsplit(
    p_value VARCHAR2,
    p_split VARCHAR2 := ',')
  --usage: select * from table(strsplit('1,2,3,4,5'))
  RETURN strsplit_type pipelined
IS
  v_idx       INTEGER;
  v_str       VARCHAR2(500);
  v_strs_last VARCHAR2(4000) := p_value;
BEGIN
  IF p_value IS NULL OR LENGTH(trim(p_value))<1 THEN
    RETURN;
  END IF;
IF p_split IS NULL OR LENGTH(trim(p_split))<1 THEN
  pipe row(p_value);
  RETURN;
END IF;
LOOP
  v_idx := instr(v_strs_last, p_split);
  EXIT
WHEN v_idx     = 0;
  v_str       := SUBSTR(v_strs_last, 1, v_idx - 1);
  v_strs_last := SUBSTR(v_strs_last, v_idx    + 1);
  pipe row(v_str);
END LOOP;
pipe row(v_strs_last);
RETURN;
END strsplit;

函数调用示例:

select * from table(strsplit('1,2,3,4,5'));
select * from table(strsplit(null));
select * from table(strsplit('1,2,3',null));

参考资料
[1] oracle的split函数. http://www.cnblogs.com/linbaoji/archive/2009/09/17/1568252.html
[2] Oracle字符分隔函数(split). http://www.cnblogs.com/yudy/archive/2012/07/18/2597874.html