CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000); CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN str_split PIPELINED AS v_length NUMBER := LENGTH(p_string); v_start NUMBER := 1; v_index NUMBER; BEGIN WHILE(v_start <= v_length) LOOP v_index := INSTR(p_string, p_delimiter, v_start); IF v_index = 0 THEN PIPE ROW(SUBSTR(p_string, v_start)); v_start := v_length + 1; ELSE PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start)); v_start := v_index + 1; END IF; END LOOP; RETURN; END splitstr; 创建完毕后,我们来测试一下,例如执行如下SQL:select * from table(splitstr('Hello,Cnblogs!',',')); 其输出结果为一个两行的表,如下图:
select regexp_substr('123,456,789','[0-9]+',1,1)
,regexp_substr('123,456,789','[0-9]+',1,2)
,regexp_substr('123,456,789','[0-9]+',1,3)
from dual;
REG REG REG
--- --- ---
123 456 789
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start); IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP; RETURN;
END splitstr;
创建完毕后,我们来测试一下,例如执行如下SQL:select * from table(splitstr('Hello,Cnblogs!',','));
其输出结果为一个两行的表,如下图:
ID VALUE
1 004-007号
2 004-006号
3 10188
4 10239
5 004-005号
6 010-003号
7 010-002号
8 030-001号
9 030-002号
10 006号
11 008-003号
12 054-001号
13 038
14 012
15 170-002号
16 181-001号
17 181-002号
18 175-003号
19 008-008号
20 175-006号
21 170-006号
22 170-005号
23 175-001号两种快捷的实现方式如下:1、借助系统函数
[php]
SQL> select rownum id, column_value value from table(sys.odcivarchar2list('004-007号','004-006号','10188','10239','004-005号','010-003号','010-002号','030-001号','030-002号','006号','008-003号','054-001号','038','012','170-002号','181-001号','181-002号','175-003号','008-008号','175-006号','170-006号','170-005号','175-001号')); ID VALUE
---------- ------------------------------
1 004-007号
2 004-006号
3 10188
4 10239
5 004-005号
6 010-003号
7 010-002号
8 030-001号
9 030-002号
10 006号
11 008-003号
12 054-001号
13 038
14 012
15 170-002号
16 181-001号
17 181-002号
18 175-003号
19 008-008号
20 175-006号
21 170-006号
22 170-005号
23 175-001号23 rows selected.
--
[/php]2、借助正则表达式
[php]
SQL> select rownum rn,TRIM(REGEXP_SUBSTR('004-007号,004-006号,10188,10239,004-005号,010-003号,010-002号,030-001号,030-002号,006号,008-003号,054-001号,038,012,170-002号,181-001号,181-002号,175-003号,008-008号,175-006号,170-006号,170-005号,175-001号', '[^,]+', 1, level)) value from dual
2 connect by level <= length(regexp_replace('004-007号,004-006号,10188,10239,004-005号,010-003号,010-002号,030-001号,030-002号,006号,008-003号,054-001号,038,012,170-002号,181-001号,181-002号,175-003号,008-008号,175-006号,170-006号,170-005号,175-001号','[^,]*'))+1; RN VALUE
---------- ------------------------------
1 004-007号
2 004-006号
3 10188
4 10239
5 004-005号
6 010-003号
7 010-002号
8 030-001号
9 030-002号
10 006号
11 008-003号
12 054-001号
13 038
14 012
15 170-002号
16 181-001号
17 181-002号
18 175-003号
19 008-008号
20 175-006号
21 170-006号
22 170-005号
23 175-001号23 rows selected.