oracle自定义split分割函数
时间：2021-07-01 10:21:17
帮助过：141人阅读
create or replace FUNCTION fn_rme_split(p_str
IN VARCHAR2,
2 p_delimiter
IN VARCHAR2)
3 RETURN rme_split
4 PIPELINED
IS
5 j
INT :
= 0;
6 i
INT :
= 1;
7 len INT :
= 0;
8 len1
INT :
= 0;
9 str VARCHAR2(
4000);
10 BEGIN
11 len :
= LENGTH(p_str);
12 len1 :
= LENGTH(p_delimiter);
13
14 WHILE j
< len LOOP
15 j :
= INSTR(p_str, p_delimiter, i);
16 IF j
= 0 THEN
17 j :
= len;
18 str :
= SUBSTR(p_str, i);
19 PIPE ROW(
str);
20 IF i
>= len THEN
21 EXIT;
22 END IF;
23 ELSE
24 str :
= SUBSTR(p_str, i, j
- i);
25 i :
= j
+ len1;
26 PIPE ROW(
str);
27 END IF;
28 END LOOP;
29 RETURN;
30 END fn_rme_split;
测试如下：
1 select temp.ass_code
2 from (SELECT COLUMN_VALUE AS ass_code
3 FROM TABLE(rme_delete.fn_rme_split(‘a,b,c,d,e,f ‘,‘,‘))) temp;
执行结果如下：
oracle自定义split分割函数
标签：nbsp png class instr lin col acl UNC create
