How to get the nth string from the delimited strings using REGEXP_SUBSTR in Oracle

Question

I have a string which is delimited by # and I want the third set / cell in the string.

For example:

select REGEXP_SUBSTR( 'abc#def##########xyz','[^#]+', 1,1,null) from dual;

Outputs: abc

select REGEXP_SUBSTR( 'abc#def##########xyz','[^#]+', 1,2,null) from dual;

Outputs: def

However

select REGEXP_SUBSTR( 'abc#def##########xyz','[^#]+', 1,3,null) from dual;

Outputs: xyz

But what I expect is to get null since the third cell between ## is empty.

Answer 1

This is a familiar issue. Use a different pattern as that answer suggests:

select REGEXP_SUBSTR( 'abc#def##########xyz','(.*?)(#|$)', 1, 1, null, 1) from dual;

abc

select REGEXP_SUBSTR( 'abc#def##########xyz','(.*?)(#|$)', 1, 2, null, 1) from dual;

def

select REGEXP_SUBSTR( 'abc#def##########xyz','(.*?)(#|$)', 1, 3, null, 1) from dual;

(null)

select REGEXP_SUBSTR( 'abc#def##########xyz','(.*?)(#|$)', 1, 12, null, 1) from dual;

xyz

Or get all of them at once with a hierarchical query (or recursive CTE):

select level as pos,
  REGEXP_SUBSTR( 'abc#def##########xyz','(.*?)(#|$)', 1, level, null, 1) as result
from dual
connect by level <= regexp_count('abc#def##########xyz', '#') + 1;

       POS RESULT              
---------- --------------------
         1 abc                 
         2 def                 
         3 (null)              
         4 (null)              
         5 (null)              
         6 (null)              
         7 (null)              
         8 (null)              
         9 (null)              
        10 (null)              
        11 (null)              
        12 xyz                 

12 rows selected. 

Answer 2

How about SUBSTR + INSTR combination?

SQL> with test (col) as (select 'abc#def##########xyz' from dual)
  2  select substr(col, instr(col, '#', 1, 2) + 1,
  3                     instr(col, '#', 1, 3) - instr(col, '#', 1, 2) - 1
  4               ) third_string,
  5         --
  6         substr(col, instr(col, '#', 1, 1) + 1,
  7                     instr(col, '#', 1, 2) - instr(col, '#', 1, 1) - 1
  8               ) second_string
  9  from test;

THIRD_STRING    SECOND_STRING
--------------- ---------------
                def

SQL>

The second_string explained (a simpler case, as it actually returns something):

• the first INSTR line finds the 2nd appearance of the # character
• the second INSTR line finds the 3rd appearance of the # character and subtracts the 2nd appearance (so it creates a substring length)