Oracle 正则表达式行转列

发布时间：2020-12-12 16:32:58 所属栏目：百科来源：网络整理

导读：原始的数据： aa,bb,cc,dd ee,ff,gg,hh 想要得到的结果： aa bb cc dd ... 先弄一条试试： SQL with t as( select 1 id,'aa,dd' names from dual ) select id,REGEXP_SUBSTR(names,'[^,]+',1,level) name, 'REGEXP_SUBSTR('''||names|| ',''[^,]+'','||level

原始的数据：

aa,bb,cc,dd

ee,ff,gg,hh

想要得到的结果：

...

先弄一条试试：

SQL> with t as(

select 1 id,'aa,dd' names from dual
)
select id,REGEXP_SUBSTR(names,'[^,]+',1,level) name,
'REGEXP_SUBSTR('''||names|| ',''[^,]+'','||level||')' exec_s from t
connect by level <=regexp_count(names,',')+1
order by name;
ID NAME EXEC_S
---- ------ --------------------------------------------
1 aa REGEXP_SUBSTR('aa,dd,1)
1 bb REGEXP_SUBSTR('aa,2)
1 cc REGEXP_SUBSTR('aa,3)
1 dd REGEXP_SUBSTR('aa,4)
已用时间: 00: 00: 00.01

这么写发现结果集不对：

SQL> with t as(
select 1 id,dd' names from dual
union all
select 2 id,'ee,hh' names from dual
)
select id,
'REGEXP_SUBSTR('''||names|| ',')+1
order by name;
ID NAME EXEC_S
--- ------ ----------------------------------------------
1 aa REGEXP_SUBSTR('aa,2)
1 bb REGEXP_SUBSTR('aa,3)
1 cc REGEXP_SUBSTR('aa,4)
1 dd REGEXP_SUBSTR('aa,4)
2 ee REGEXP_SUBSTR('ee,hh,1)
2 ff REGEXP_SUBSTR('ee,2)
2 ff REGEXP_SUBSTR('ee,2)
2 gg REGEXP_SUBSTR('ee,3)
2 gg REGEXP_SUBSTR('ee,3)
2 hh REGEXP_SUBSTR('ee,4)
2 hh REGEXP_SUBSTR('ee,4)
已选择30行。

已用时间: 00: 00: 00.06

原因是多行的话产生了上下级关系：

SQL> with t as(
select 1 id,hh' names from dual
) select level,names,sys_connect_by_path(id,'--') as full_path,
count(1) over(partition by names,level) as cnt
from t
connect by level <= regexp_count(names,')+1
order by names,level;
LEVEL NAMES FULL_PATH CNT
---------- ----------- -------------------- ----------
1 aa,dd --1 1
2 aa,dd --2--1 2
2 aa,dd --1--1 2
3 aa,dd --1--2--1 4
3 aa,dd --1--1--1 4
3 aa,dd --2--2--1 4
3 aa,dd --2--1--1 4
4 aa,dd --1--1--2--1 8
4 aa,dd --1--1--1--1 8
4 aa,dd --2--2--2--1 8
4 aa,dd --2--2--1--1 8
4 aa,dd --2--1--2--1 8
4 aa,dd --2--1--1--1 8
4 aa,dd --1--2--2--1 8
4 aa,dd --1--2--1--1 8
1 ee,hh --2 1
2 ee,hh --1--2 2
2 ee,hh --2--2 2
3 ee,hh --2--2--2 4
3 ee,hh --2--1--2 4
3 ee,hh --1--2--2 4
3 ee,hh --1--1--2 4
4 ee,hh --1--1--1--2 8
4 ee,hh --1--2--1--2 8
4 ee,hh --2--2--2--2 8
4 ee,hh --1--2--2--2 8
4 ee,hh --2--1--2--2 8
4 ee,hh --2--1--1--2 8
4 ee,hh --2--2--1--2 8
4 ee,hh --1--1--2--2 8
只限制本行拆分：

SQL> with t as(
select 1 id,dd' names from dual
union all
select 2 id,hh' names from dual
)
select level,level) as cnt from t
connect by prior names=names
and level<=regexp_count(names,')+1
and prior SYS_GUID() is not null;
LEVEL NAMES FULL_PATH CNT
---------- ----------- -------------------- ----------
1 aa,dd --1 1
2 aa,dd --1--1 1
3 aa,dd --1--1--1 1
4 aa,dd --1--1--1--1 1
1 ee,hh --2 1
2 ee,hh --2--2 1
3 ee,hh --2--2--2 1
4 ee,hh --2--2--2--2 1

改写：

SQL> with t as(
select 1 id,'||level||')' exec_s from t
connect by prior names=names
and level<=regexp_count(names,')+1
and prior SYS_GUID() is not null order by name; ID NAME EXEC_S ---- ------- --------------------------------------------- 1 aa REGEXP_SUBSTR('aa,1) 1 bb REGEXP_SUBSTR('aa,2) 1 cc REGEXP_SUBSTR('aa,3) 1 dd REGEXP_SUBSTR('aa,4) 2 ee REGEXP_SUBSTR('ee,1) 2 ff REGEXP_SUBSTR('ee,2) 2 gg REGEXP_SUBSTR('ee,3) 2 hh REGEXP_SUBSTR('ee,4) 已选择8行。已用时间: 00: 00: 00.05

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!