oracle中使用connect展开带有分隔符字符串时带有空值时的处理

使用版本为11g及以上

对于aaa,bbb,ddd,ggg这样的字符串，可以使用connect语句将其拆分

with tab1 as (
select 'aaa,bbb,ddd,ggg' str from dual
)
select t1.str,
       regexp_substr(t1.str, '[^,]+', 1, level) res,
       level
  from tab1 t1
connect by level <= regexp_count(t1.str, ',') + 1
;

STR	RES	LEVEL
aaa,bbb,ddd,ggg	aaa	1
aaa,bbb,ddd,ggg	bbb	2
aaa,bbb,ddd,ggg	ddd	3
aaa,bbb,ddd,ggg	ggg	4

网上经常可以找到这样的例子，这写的也没有什么问题。但是当所分隔的值中存在null时，就有问题了。

with tab1 as (
select 'aaa,bbb,,ddd,,,ggg' str from dual
)
select t1.str,
       regexp_substr(t1.str, '[^,]+', 1, level) res,
       level
  from tab1 t1
connect by level <= regexp_count(t1.str, ',') + 1
;

STR	RES	LEVEL
aaa,bbb,ddd,ggg	aaa	1
aaa,bbb,ddd,ggg	bbb	2
aaa,bbb,ddd,ggg	ddd	3
aaa,bbb,ddd,ggg	ggg	4
aaa,bbb,ddd,ggg		5
aaa,bbb,ddd,ggg		6
aaa,bbb,ddd,ggg		7

而我们真正想要的结果应该是这样的

STR	RES	LV
aaa,bbb,ddd,ggg	aaa	1
aaa,bbb,ddd,ggg	bbb	2
aaa,bbb,ddd,ggg		3
aaa,bbb,ddd,ggg	ddd	4
aaa,bbb,ddd,ggg		5
aaa,bbb,ddd,ggg		6
aaa,bbb,ddd,ggg	ggg	7

这里本应该使用更加巧妙的正则表达式来解决的，但是我一时没想到，就先用下面的方法来凑合了

with tab1 as (
select 'aaa,bbb,,ddd,,,ggg' str from dual
)
, tab2 as (
select t1.str,
       regexp_substr(t1.str, '[^,]*', 1, level) res, 
       lag(regexp_substr(t1.str, '[^,]*', 1, level)) over(order by level) lg,
       max(regexp_substr(t1.str, '[^,]*', 1, level)) 
           over(order by level rows between current row and unbounded following) mx,
       level lv
  from tab1 t1
connect by level <= regexp_count(t1.str, ',') * 2 + 1
)
select t1.str,
       t1.res,
       row_number() over(order by t1.lv) lv
  from tab2 t1
 where t1.mx is not null
    and (t1.res is not null
    or (t1.res is null and t1.lg is null)
    )
;