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
– Sami Kuhmonen, Commented Jun 8, 2015 at 8: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
– Lalit Kumar B, Commented Jun 8, 2015 at 8:37

Gary_W · Accepted Answer · 2015-07-27 17:55:17Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related