oracle根据分号截取字符串,根据指定分隔符拆分字符串

在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@]