在10G中可用正则表达式实现，但9I并不支持。
9i
---------------------------------------------------------------------------------------
select substr('11,22,3377,99', pos, len)
From (select nvl(lag(position) over(order by rownum) + 1, 1) pos,
position - nvl(lag(position) over(order by rownum) + 1, 1) len
from (select decode(instr('11,22,3377,99', ',', 1, rownum),
0,
length('11,22,3377,99') + 1,
instr('11,22,3377,99', ',', 1, rownum)) position
from dual
connect by rownum <=
length('11,22,3377,99') -
length(replace('11,22,3377,99', ',', '')) + 1));
STR
-------
11
22
3377
99
其中的细分步骤：
-- 计算每个分隔符的位置
select decode(instr('11,22,3377,99', ',', 1, rownum),
0,
length('11,22,3377,99') + 1,
instr('11,22,3377,99', ',', 1, rownum)) position
from dual
connect by rownum <=
-- 计算分隔符','的个数
length('11,22,3377,99') -
length(replace('11,22,3377,99', ',', '')) + 1
POSITION
----------
3
6
11
14
--计算每段字符的起始位置和长度，为截取字符串做准备
select nvl(lag(position) over(order by rownum) + 1, 1) pos,
position - nvl(lag(position) over(order by rownum) + 1, 1) len
from (select decode(instr('11,22,3377,99', ',', 1, rownum),
0,
length('11,22,3377,99') + 1,
instr('11,22,3377,99', ',', 1, rownum)) position
from dual
connect by rownum <= length('11,22,3377,99') -
length(replace('11,22,3377,99', ',', '')) + 1)
POSITION LEN
----------------
1 2
4 2
7 4
12 2
10g
---------------------------------------------------------------------------------------
select regexp_substr('11,22,3377,99', '([^,]+)', 1, rownum) str
from dual
connect by rownum <
(length(regexp_replace('11,22,3377,99', '[^,]', '')) + 2)
STR
-------
11
22
3377
99
测试：
CREATE OR REPLACE PROCEDURE SP_REGEXP_TEST(P_STR IN VARCHAR2) AS
BEGIN
FOR V_CUR IN (SELECT REGEXP_SUBSTR(P_STR, '([^,]+)', 1, ROWNUM) STR
FROM DUAL
CONNECT BY ROWNUM <=
(LENGTH(REGEXP_REPLACE(P_STR, '[^,]', '')) + 1)) LOOP
DBMS_OUTPUT.PUT_LINE(V_CUR.STR);
END LOOP;
END;[@more@]
