首先,Oracle是没有split函数的,因为Oracle看不到和数组类似的结构,因此这里参照网上的fn_split的函数做了点改进,下面的可以直接用
--网上的fn_split好像过滤掉null值,这里做了点改进,请注意区别
转载:http://zyj0021.iteye.com/blog/816633
--网上的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