[译]在Oracle中将逗号分隔的字符串拆分为行

在Oracle中将逗号分隔的字符串拆分为行

遇到的问题

在数据库字段中,有时候开发在存数据的时候,只考虑方便,会对一些单体多值的数据进行一个 字符串+特殊符号 进行数据存值; eg 下图colB的值

colA	colB
1	split,into,rows

对于程序处理起来，可能比较便利，但对于SQL处理来说，存在一定难度，个人还是建议寻求其他的存储格式吧

单行数据处理

方式一：针对于规范的使用一定分隔规则的可以如此处理

with rws as (
  select 'split,into,rows' str from dual
)
  select regexp_substr (
           str,
           '[^,]+',
           1,
           level
         ) value
  from   rws
  connect by level <= 
    length ( str ) - length ( replace ( str, ',' ) ) + 1;
    
---Result ---
VALUE   
split    
into     
rows

方式二：针对前后字符串存有遗留分隔符的

with rws as (
  select ',leading,commas,and,trailing,' str from dual
)
  select regexp_substr (
           str,
           '[^,]+',
           1,
           level
         ) value
  from   rws
  connect by level <= 
    length ( trim ( both ',' from str ) ) - 
    length ( replace ( str, ',' ) ) + 1;
    
------------RESULT---------------------------
VALUE      
leading     
commas      
and         
trailing

那么其他的分隔符号的，怎么处理呢

通过上面的两段SQL处理来看，可以看得出来，在对字符串进行处理的时候，使用的是REGEXP_SUBSTR 进行正则匹配 [^,]+ 这里匹配的是逗号，同理，也可以匹配其他的符号 eg: [^;]+

with rws as (
  select 'split;semicolons;into;rows' str from dual
)
  select regexp_substr (
           str,
           '[^;]+',
           1,
           level
         ) value
  from   rws
  connect by level <= 
    length ( str ) - 
    length ( replace ( str, ';' ) ) + 1;
  
 -----------RESULT-------------------------
VALUE        
split         
semicolons    
into          
rows

上面说的都是针对单行记录进行处理，那么针对整个表的某个字符串，要怎么处理呢

Demo Table

create table csvs (
  csv_id   integer
    primary key,
  csv_text varchar2(1000)
);

insert into csvs 
  values ( 1, 'split,into,rows' );
insert into csvs 
  values ( 2, 'even,more,values,to,extract' );
  
commit;

在Oracle12c及更高的版本，可使用 lateral join

select csv_id, regexp_substr (
         csv_text,
         '[^,]+',
         1,
         rn
       ) val
from   csvs
cross  join lateral (
  select level rn from dual
  connect by level <=   
    length ( csv_text ) - length ( replace ( csv_text, ',' ) ) + 1
);

CSV_ID VAL       
     1 split      
     1 into       
     1 rows       
     2 even       
     2 more       
     2 values     
     2 to         
     2 extract

依然使用Connect By进行处理

select csv_id, regexp_substr (
         csv_text,
         '[^,]+',
         1,
         level
       ) val
from   csvs 
connect by level <=   
  length ( csv_text ) - length ( replace ( csv_text, ',' ) ) + 1
and    prior csv_text = csv_text
and    prior sys_guid () is not null;

CSV_ID VAL       
     2 even       
     2 more       
     2 values     
     2 to         
     2 extract    
     1 split      
     1 into       
     1 rows

后注：

• 在实际使用中，性能其实不大好，跟Connect By的原理有关系

借鉴：
https://blogs.oracle.com/sql/post/split-comma-separated-values-into-rows-in-oracle-database