Oracle的split

首先，Oracle是没有split函数的，因为Oracle看不到和数组类似的结构，因此这里参照网上的fn_split的函数做了点改进，下面的可以直接用
--网上的fn_split好像过滤掉null值，这里做了点改进，请注意区别

1.create or replace type ty_str_split is table of varchar2 (4000);   
2.create or replace function fn_split (p_str in varchar2, p_delimiter in varchar2)   
3.    return ty_str_split   
4.is  
5.    j int := 0;   
6.    i int := 0;   
7.    getLen int :=0;   
8.    len int := 0;   
9.    len1 int := 0;   
10.    str varchar2 (4000);   
11.    str_split ty_str_split := ty_str_split ();   
12.    v_isFound number:=0;   
13.begin  
14.    if p_delimiter is not null then  
15.        select instr(p_str,p_delimiter) into v_isFound from dual;   
16.        if v_isFound=0  then--没找到   
17.            str_split.extend;   
18.            str_split (str_split.count) := p_str;   
19.        else  
20.            len := length (p_str);   
21.            len1 := length (p_delimiter);   
22.            while j <= len   
23.            loop   
24.                i:=i+1;   
25.                j := instr (p_str, p_delimiter,i);   
26.                getLen:=j-i;   
27.  
28.                if j = 0  then  
29.                    str := substr (p_str, i);   
30.                    str_split.extend;   
31.                    str_split (str_split.count) := str;   
32.                    exit;   
33.                else  
34.                    str := substr (p_str, i, getLen);   
35.                    str_split.extend;   
36.                    str_split (str_split.count) := str;   
37.                    i := j-1 + len1;   
38.                    end if;   
39.            end loop;   
40.        end if ;   
41.    else  
42.        str_split.extend;   
43.        str_split (str_split.count) := p_str;   
44.    end if ;   
45.    return str_split;   
46.end fn_split;   
47.  
48.--具体用法   
49.select t.column_value,rownum row_ from  
50.table(   
51.    cast (   
52.        fn_split ('1,2,3,4,5,,7,,', ',') as ty_str_split   
53.    )   
54.) t  

转载：http://zyj0021.iteye.com/blog/816633