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博客 ” ，链接：https://blog.itpub.net/15742414/viewspace-611595/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
查看Oracle中的锁
下一篇：
基于函数的索引
请登录后发表评论
登录
全部评论
