字符分隔函数是很常用的, 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