Oracle 一行字符串拆分为多行

测试数据

--单条数据
with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
)
select * from z_branch;

--多条数据
with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
  union all
  select 'BR1002' brid, 'BR1002.BR1003' br_level from dual
  union all
  select 'BR1003' brid, 'BR1001.BR1003' br_level from dual
)
select * from z_branch;

1. 单个字符串或单条数据拆分

如果表中只有单行数据或拆分单个字符串，可以使用。网上大部分讲的都是单条数据的拆分，如果是多条数据拆分则不适用。

with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
)
select level, 
       regexp_count(br_level, '\.') + 1,
       regexp_substr(br_level, '[^.]+', 1, level) s_brid
  from z_branch t
connect by level <= regexp_count(br_level, '\.') + 1;

regexp_count(br_level, ‘.’) 统计字符.的个数
regexp_substr(br_level, ‘[^.]+’, 1, level) br_level字段从位置1开始使用字符.拆分，level指取拆分后第level个字符串

或

with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
)
select level,
       length(brid) - length(replace(brid, '.', '')) + 1,
       regexp_substr(br_level, '[^.]+', 1, level) s_brid
  from z_branch t
connect by level <= length(br_level) - length(replace(br_level, '.', '')) + 1;

错误示例

--结果不符合预期
with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
  union all
  select 'BR1002' brid, 'BR1002.BR1003' br_level from dual
  union all
  select 'BR1003' brid, 'BR1001.BR1003' br_level from dual
)
select brid,
       level, 
       regexp_count(br_level, '\.') + 1,
       regexp_substr(br_level, '[^.]+', 1, level) s_brid
  from z_branch t
connect by level <= regexp_count(br_level, '\.') + 1;

-- 不符合预期
with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
  union all
  select 'BR1002' brid, 'BR1002.BR1003' br_level from dual
  union all
  select 'BR1003' brid, 'BR1001.BR1003' br_level from dual
)
select brid,
       level, 
       regexp_count(br_level, '\.') + 1,
       regexp_substr(br_level, '[^.]+', 1, level) s_brid
  from z_branch t
 where t.brid = 'BR1001'
connect by level <= regexp_count(br_level, '\.') + 1;

从此结果可知数据是先生成后，然后使用where条件筛选

2. 多条数据拆分

1、方法1

--正确
with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
  union all
  select 'BR1002' brid, 'BR1002.BR1003' br_level from dual
  union all
  select 'BR1003' brid, 'BR1001.BR1003' br_level from dual
)
select brid,
       level, 
       regexp_count(br_level, '\.') + 1 as row_cnt,
       regexp_substr(br_level, '[^.]+', 1, level) as s_brid
  from z_branch t
connect by level <= regexp_count(br_level, '\.') + 1
   and t.brid = prior t.brid
   and prior dbms_random.value > 0;

2、方法2

--正确 借助伪列
with z_branch as(
  select 'BR1001' brid, 'BR1001.BR1002.BR1003' br_level from dual
  union all
  select 'BR1002' brid, 'BR1002.BR1003' br_level from dual
  union all
  select 'BR1003' brid, 'BR1001.BR1003' br_level from dual
),
z_level as(
  select level lv from dual connect by level < 10
) 
select t.brid,
       a.lv, 
       regexp_count(t.br_level, '\.') + 1 as row_cnt,
       regexp_substr(t.br_level, '[^.]+', 1, a.lv) as s_brid
  from z_branch t
 inner join z_level a
    on a.lv <= regexp_count(t.br_level, '\.') + 1;

注意：创建的伪列行数，要大于其中字符串拆分后数量最大值，本示例中字符串拆分后最大值是3，所以只要伪列行数大于3即可。