oracle分割字符串转多行

最新推荐文章于 2023-11-09 10:30:23 发布

嵒碞

最新推荐文章于 2023-11-09 10:30:23 发布

阅读量1.7k

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_40562026/article/details/122684912

版权

create table test
(
  pid           VARCHAR2(50),
  user_name     VARCHAR2(200)
)

insert into test(pid, user_name) values ('1', 'a,b,c');
insert into test(pid, user_name) values ('2', 'd,e,f');
insert into test(pid, user_name) values ('3', 'g,h,i,j,k');

查询sql

select t.pid, regexp_substr(t.user_name, '[^,]+', 1, level) user_name, level
  from test t
connect by prior t.pid = t.pid
       and level <= regexp_count(t.user_name, '[^,]+')
       and prior DBMS_RANDOM.VALUE() IS NOT NULL

prior t.pid = t.pid 和 and prior DBMS_RANDOM.VALUE() IS NOT NULL 一定要有, 不然逗号分割的多了会非常慢

prior t.pid = t.pid 标记关系

prior DBMS_RANDOM.VALUE() IS NOT NULL 解决死循环

参考:ORACLE逗号分割的字符串转多行(转载) - 语讷言拙 - 博客园