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.

If you can do something simply with regular string functions, usually regexps are *not* more efficient, or powerful. They are not something you should strive to use just because. — Sami Kuhmonen, Jun 08 '15 at 08:35
**Regular expressions** are not more efficient than the regular **SUBSTR** and **INSTR** functions. They are resource consuming and CPU Intensive operations. The only case when regexp seems useful is when you have a complicated query and you could shorten it with regexp features. — Lalit Kumar B, Jun 08 '15 at 08:37

score 6 · Accepted Answer · answered Jul 27 '15 at 17:55

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.

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

1 Answers1

Linked