[SOLVED] Split string using pl/sql using connect level on null value

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

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?

Solution

Because I used single characters as example in my question, but in practicality I'm using long strings in my project, for example 'How~do~I~do~this'

I came across this solution from OTN Oracle.com, thanks to chris227.

SELECT CAST(REGEXP_SUBSTR (str, '(.*?)(~|$)', 1, level, null, 1) AS CHAR(12))  output
FROM (select 'How~do~I~do~this' as str from dual)
CONNECT BY level <= regexp_count(str, '~') + 1;

This will work even with single characters.

Hope this will help others looking for similair solutions.