Oracle分拆字符串到多条记录（split row in many rows）

create table split_string_test(

  id integer  primary key,

  test_string varchar2(500)

);

insert into split_string_test values(1, '10,11,12,13,14,22');

insert into split_string_test values(2, '22,23,24');

insert into split_string_test values(3, '6,7,8,9');

WITH  cntr  AS

( SELECT  LEVEL  AS lvl

  FROM  dual

  CONNECT BY  LEVEL  <= 1 +  (

          SELECT  MAX ( length(a.test_string) - length(replace(a.test_string, ',')) + 1) 

          FROM  split_string_test a

          )                

) 

SELECT  b.id, b.test_string , lvl, REGEXP_SUBSTR( b.test_string, '([^,]+)', 1, lvl)  AS split_str

FROM  split_string_test b , cntr 

where  (lvl  <=  length(b.test_string) - length(replace(b.test_string, ',')) + 1)

and (REGEXP_SUBSTR  ( b.test_string, '([^,]+)', 1, lvl)  IS NOT NULL

OR  b.test_string  IS NULL )

ORDER BY  b.id, lvl;

注：

SELECT  MAX ( length(a.test_string) - length(replace(a.test_string, ',')) + 1) 

          FROM  split_string_test a

取字符串分拆最大的数

查询结果如下：

序号   ID    test_string                    lvl       split_str

1     1   10,11,12,13,14,22  1        10

2     1   10,11,12,13,14,22  2        11

3     1   10,11,12,13,14,22  3        12

4     1   10,11,12,13,14,22  4        13

5     1   10,11,12,13,14,22  5        14

6     1   10,11,12,13,14,22  6        22

7     2   22,23,24                   1        22

8     2   22,23,24                   2        23

9     2   22,23,24                   3        24

10      3   6,7,8,9                           1        6

11      3   6,7,8,9                           2        7

12      3   6,7,8,9                           3        8

13      3   6,7,8,9                           4        9

SELECT rtrim(regexp_substr(t.test_string || ',', '([^,]+)', 1, level), ',') AS split_right

,t.*

FROM  split_string_test t

CONNECT BY id   =  prior id

AND PRIOR dbms_random.VALUE IS NOT NULL

AND LEVEL <= length(regexp_replace(t.test_string || ',', '[^' || ',' || ']', NULL)) 

结果一样， 不过在Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 

运行，会报 ora-01436 connect by loop in user data 错误，

在 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

没有问题

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15742414/viewspace-611595/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15742414/viewspace-611595/