Split string using pl/sql using connect level on null value

Question

I'm using this following code in Oracle pl/sql (Version: Oracle Database 11g Release 11.2.0.1.0)

select regexp_substr('A~B~C','[^~]+',1,level) output
from dual
connect by level <= length(regexp_replace('A~B~C','[^~]+')) + 1

which gives the following results

row1: A
row2: B
row3: C

That's perfect, however should I want to give a null value, ie:

select regexp_substr('~B~C','[^~]+',1,level) output
from dual
connect by level <= length(regexp_replace('~B~C','[^~]+')) + 1

I expected and wanted the following:

row1: <null>
row2: B
row3: C

but got this output:

row1: B
row2: C
row3: null

Am I doing the pl/sql code wrong? How can I make it work right?

Answer 1

you can combine INSTR und SUBSTR to achive the desiered result:

select  
  str, 
  replace(substr(str, 
                 case level 
                 when 1 then 0 
                 else instr( str, '~',1, level-1) 
                 end 
                  +1,
                 1
                ), '~')
from ( select 'A~B~C~D~E' as str from dual)
connect by level <= length(regexp_replace(str,'[^~]+')) + 1
;