How can I use a regular expression to split a string using the string as delimiter?
I am trying to split a string using a string as delimiter in an Oracle storage routine. I can use it easily instr
, but I am trying to learn how to do it with a regex as I understand that it is powerful and efficient.
After reading some articles, I thought I could do this (the expected output was " Hello
"):
select regexp_substr('Hello My Delimiter World', '( My Delimiter )+', 1, 1)
from dual
Result:
My separator
and (expected output was " World
"):
select regexp_substr('Hello My Delimiter World', '( My Delimiter )+', 1, 2)
from dual
Result:
zero
What's the correct regex_substr value for this requirement?
EDIT: I'm looking for something like below. In one pass, it selects a substring within a string:
eg. select regexp_substr('Hello World', '[^ ]+', 1, 2) from dual
But this example only works with one character.
source to share
Try these methods.
This gets the first element you originally requested:
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 entire string. Added NULL elements to indicate that they work on 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.
source to share