ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle分拆字符串到多条记录(split row in many rows)

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

原创 Linux操作系统 作者:mscpx 时间:2009-08-07 14:48:50 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 查看Oracle中的锁
下一篇: 基于函数的索引
请登录后发表评论 登录
全部评论

注册时间:2009-06-01

  • 博文量
    36
  • 访问量
    59762