Oracle 正则表达式行转列

女爷i 2022-06-17 05:07 234阅读 0赞

原始的数据：

aa,bb,cc,dd

ee,ff,gg,hh

想要得到的结果：

...

**先弄一条试试：**

SQL> with t as(

select 1 id,'aa,bb,cc,dd' names from dual  
    )  
    select  id,REGEXP\_SUBSTR(names, '\[^,\]+', 1, level) name,  
     'REGEXP\_SUBSTR('''||names|| ',''\[^,\]+'', 1, '||level||')' exec\_s  from t  
    connect by level <=regexp\_count(names,',')+1  
    order by name;  
  ID NAME   EXEC\_S  
\---- ------ --------------------------------------------  
   1 aa     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 1)  
   1 bb     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 2)  
   1 cc     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 3)  
   1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
已用时间:  00: 00: 00.01

**这么写发现结果集不对：**

SQL> with t as(  
       select 1 id,'aa,bb,cc,dd' names from dual  
       union all  
       select 2 id,'ee,ff,gg,hh' names from dual  
     )  
     select  id,REGEXP\_SUBSTR(names, '\[^,\]+', 1, level) name,  
      'REGEXP\_SUBSTR('''||names|| ',''\[^,\]+'', 1, '||level||')' exec\_s from t  
     connect by level <=regexp\_count(names,',')+1  
     order by name;  
 ID NAME   EXEC\_S  
\--- ------ ----------------------------------------------  
  1 aa     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 1)  
  1 bb     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 2)  
  1 bb     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 2)  
  1 cc     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 3)  
  1 cc     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 3)  
  1 cc     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 3)  
  1 cc     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 3)  
  1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
  1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
  1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
  1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
  1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
  1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
  1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
  1 dd     REGEXP\_SUBSTR('aa,bb,cc,dd,'\[^,\]+', 1, 4)  
  2 ee     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 1)  
  2 ff     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 2)  
  2 ff     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 2)  
  2 gg     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 3)  
  2 gg     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 3)  
  2 gg     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 3)  
  2 gg     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 3)  
  2 hh     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 4)  
  2 hh     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 4)  
  2 hh     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 4)  
  2 hh     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 4)  
  2 hh     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 4)  
  2 hh     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 4)  
  2 hh     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 4)  
  2 hh     REGEXP\_SUBSTR('ee,ff,gg,hh,'\[^,\]+', 1, 4)  
已选择30行。

已用时间:  00: 00: 00.06

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

SQL> with t as(  
      select 1 id,'aa,bb,cc,dd' names from dual  
      union all  
      select 2 id,'ee,ff,gg,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,bb,cc,dd --1                           1  
         2 aa,bb,cc,dd --2--1                        2  
         2 aa,bb,cc,dd --1--1                        2  
         3 aa,bb,cc,dd --1--2--1                     4  
         3 aa,bb,cc,dd --1--1--1                     4  
         3 aa,bb,cc,dd --2--2--1                     4  
         3 aa,bb,cc,dd --2--1--1                     4  
         4 aa,bb,cc,dd --1--1--2--1                  8  
         4 aa,bb,cc,dd --1--1--1--1                  8  
         4 aa,bb,cc,dd --2--2--2--1                  8  
         4 aa,bb,cc,dd --2--2--1--1                  8  
         4 aa,bb,cc,dd --2--1--2--1                  8  
         4 aa,bb,cc,dd --2--1--1--1                  8  
         4 aa,bb,cc,dd --1--2--2--1                  8  
         4 aa,bb,cc,dd --1--2--1--1                  8  
         1 ee,ff,gg,hh --2                           1  
         2 ee,ff,gg,hh --1--2                        2  
         2 ee,ff,gg,hh --2--2                        2  
         3 ee,ff,gg,hh --2--2--2                     4  
         3 ee,ff,gg,hh --2--1--2                     4  
         3 ee,ff,gg,hh --1--2--2                     4  
         3 ee,ff,gg,hh --1--1--2                     4  
         4 ee,ff,gg,hh --1--1--1--2                  8  
         4 ee,ff,gg,hh --1--2--1--2                  8  
         4 ee,ff,gg,hh --2--2--2--2                  8  
         4 ee,ff,gg,hh --1--2--2--2                  8  
         4 ee,ff,gg,hh --2--1--2--2                  8  
         4 ee,ff,gg,hh --2--1--1--2                  8  
         4 ee,ff,gg,hh --2--2--1--2                  8  
         4 ee,ff,gg,hh --1--1--2--2                  8  
**只限制本行拆分：**

**改写：**