ORACLE - regexp_substr to return null values

Question

I have used the function regexp_substr(STR,'[^|]+',1,2), it works well at some cases but for null scenarios, it gives the third value,

1|CAT|DOG
3|HARRY|GOAT|STACK
6||LION|TIGER  --this row should return null, but the above function gives me LION

Expected Output:

CAT
HARRY
(null)

Answer 1

@Gary_W has written about the problem with using that regex pattern to split strings, precisely because of how it treats empty tags. (And is on a mission...)

The alternate approach in that post works here too, with the pipe delimiter escaped:

with t (str) as (
  select '1|CAT|DOG' from dual
  union all select '3|HARRY|GOAT|STACK' from dual
  union all select '6||LION|TIGER' from dual
)
select str, regexp_substr(str, '(.*?)(\||$)', 1, 2, null, 1) from t;

STR                REGEXP_SUBSTR(STR,
------------------ ------------------
1|CAT|DOG          CAT               
3|HARRY|GOAT|STACK HARRY             
6||LION|TIGER                        

Similarly for the third element:

select str, regexp_substr(str, '(.*?)(\||$)', 1, 3, null, 1) from t;

STR                REGEXP_SUBSTR(STR,
------------------ ------------------
1|CAT|DOG          DOG               
3|HARRY|GOAT|STACK GOAT              
6||LION|TIGER      LION              

And the fourth:

select str, regexp_substr(str, '(.*?)(\||$)', 1, 4, null, 1) from t;

STR                REGEXP_SUBSTR(STR,
------------------ ------------------
1|CAT|DOG                            
3|HARRY|GOAT|STACK STACK             
6||LION|TIGER      TIGER             

Answer 2

You function is returning right result. When you run your function,

function regexp_substr(STR,'[^|]+',1,2) 

at

6||LION|TIGER

Oracle is not finding any 'NULL' values between ||. If you put a space in between ||, it would return the correct result. See below:

Query:

WITH tbl (str)
     AS (SELECT '1|CAT|DOG' FROM DUAL
         UNION ALL
         SELECT '3|HARRY|GOAT|STACK' FROM DUAL
         UNION ALL
         SELECT '6| |LION|TIGER' FROM DUAL) --<-- Space added between | |
SELECT  REGEXP_SUBSTR (STR,
                      '[^|]+',
                      1,
                      2
                      )        
 FROM tbl

Alternately, you can replace all || characters with | | in you query and get the result.

WITH tbl (str)
     AS (SELECT '1|CAT|DOG' FROM DUAL
         UNION ALL
         SELECT '3|HARRY|GOAT|STACK' FROM DUAL
         UNION ALL
         SELECT '6||LION|TIGER' FROM DUAL)     
SELECT REGEXP_SUBSTR ((replace(STR,'||','| |')),
                      '[^|]+',
                      1,
                      2)
  FROM tbl