How can I use regex to split a string, using a string as a delimiter?

Question

I'm trying to split a string using a string as a delimiter, in an Oracle store procedure. I can use instr easily, but I'm trying to learn how to do this with regex, as I understand that it is powerful and efficient.

After reading some articles, I thought I could do this (expected result was "Hello"):

select regexp_substr('Hello My Delimiter World', '( My Delimiter )+', 1, 1)
from dual

Result:

My Delimiter

and (expected result was "World"):

  select regexp_substr('Hello My Delimiter World', '( My Delimiter )+', 1, 2)
    from dual

Result:

null

What is the correct regex_substr for this requirement?

EDIT: I'm looking for something like the below. In a single pass, it selects the sub-string within the string:

E.g. select regexp_substr('Hello World', '[^ ]+', 1, 2) from dual But this sample only works with a single character.

Answer 1

Try these methods.

This gets the first element as you originally asked for:

SQL> with tbl(str) as (
      select 'Hello My Delimiter World' from dual
    )
    SELECT REGEXP_SUBSTR( str ,'(.*?)( My Delimiter |$)', 1, 1, NULL, 1 ) AS element
    FROM   tbl;

ELEME
-----
Hello

This version parses the whole string. NULL elements added to show it works with missing elements:

SQL> with tbl(str) as (
      select ' My Delimiter Hello My Delimiter World My Delimiter  My Delimiter test My Delimiter ' from dual
    )
    SELECT LEVEL AS element,
    REGEXP_SUBSTR( str ,'(.*?)( My Delimiter |$)', 1, LEVEL, NULL, 1 ) AS element_value
    FROM   tbl
    CONNECT BY LEVEL <= regexp_count(str, ' My Delimiter ')+1;

   ELEMENT ELEMENT_VALUE
---------- --------------------
         1
         2 Hello
         3 World
         4
         5 test
         6

6 rows selected.