SQL REGEXP_SUBSTR returns null String when splitting

Question

I followed the instructions outlined in this post to split a delimited string into rows of a table:

Splitting string into multiple rows in Oracle

The answer that worked for that particular delimited string is represented in this fiddle:

Demo1

with temp as
(
    select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (
        regexp_replace(t.error, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
order by name;

Unfortunately my string is not comma delimited. It is delimited by the substring ':::'. I attempted to alter the answer to suit my situation by writing the SQL in the following fiddle:

Demo2

with temp as
(
    select 108 Name, 'test' Project, 'Err1:::Err2:::Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^:::]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (
        regexp_replace(t.error, '[^:::]+'))  + 1) as sys.OdciNumberList)) levels
order by name

As you can see I altered the test string to be delimited by ':::' and altered the regular expression to match, but the query is producing an extraneous row with a Null value for the substring returned.

Can anyone help me understand why the changes I made would be producing that extraneous row with the Null value?

Answer 1

Just use REPLACE and your standard code with ,

SqlFiddleDemo

 with temp as
(
    select 108 Name, 'test' Project, 'Err1:::Err2:::Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(REPLACE(t.error, ':::', ', '), '[^,]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(REPLACE(t.error, ':::', ', '), '[^,]+'))  + 1) as sys.OdciNumberList)) levels
order by name

Or you need to divide by length of delimeter:

SqlFiddle

with temp as
(
    select 108 Name, 'test' Project, 'Err1:::Err2:::Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1:::Err2' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^:::]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (
       regexp_replace(t.error, '[^:::]+'))/3  + 1) as sys.OdciNumberList)) levels
order by name

You can see why executing:

SELECT length (regexp_replace('Err1:::Err2:::Err3', '[^:::]+')) + 1 AS l
FROM dual

This will return 7 and your:

SELECT DISTINCT  t.name, t.project,
trim(regexp_substr(t.error, '[^:::]+', 1, levels.column_value))  as error

will try to get regexp_substr for 7 occurences where 4 of them will be NULL and in the end 4 NULL will be squashed to one NULL by DISTINCT.