How to split strings using two delimiters in Oracle 11g regexp_substr function
I have doubts to split a string using a delimiter.
First split based on, separator selects those split lines to be split based on separator
My original line: UMC12I-1234,CSM3-123,VQ,
Expected output:
UMC12I
CSM3
VQ
Each value appears as a string value
I tried option
WITH fab_sites AS (
SELECT trim(regexp_substr('UMC12I-1234,CSM3-123,VQ,', '[^,]+', 1, LEVEL)) fab_site
FROM dual
CONNECT BY LEVEL <= regexp_count('UMC12I-1234,CSM3-123,VQ,', '[^,]+')+1
)
SELECT fab_site FROM fab_sites WHERE fab_site IS NOT NULL
- split based on delimiter
Output:
UMC12I-1234 CSM3-123 VQ
How can I get the expected result? (need to split again - separator)
source to share
You can extract the words before -
using regexp_substr
using
([^,-]+)(-[^,-]+)?
The pattern will match and map to group 1 one or more characters other than ,
and -
, and then match an optional sequence -
and 1+ characters other than ,
and -
.
See regex demo .
Use this line regex_substr
instead of yours with the above expression:
SELECT trim(regexp_substr('UMC12I-1234,CSM3-123,VQ,', '([^,-]+)(-[^,-]+)?', 1, LEVEL, NULL, 1)) fab_site
Watch the online demo
source to share
You can try this query:
WITH fab_sites AS (
SELECT TRIM(',' FROM REGEXP_SUBSTR('UMC12I-1234,CSM3-123,VQ,', '(^|,)[^,-]+', 1, LEVEL)) fab_site
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT('UMC12I-1234,CSM3-123,VQ,', '(^|,)[^,-]+')
)
SELECT fab_site
FROM fab_sites;
Let's start by matching any substring that starts either at the beginning of the entire string ^
, or with a comma ,
, separator. Then we get all characters that don't match any comma or dash -
. Once we have this substring, we strip off the remainder of it.
PS I think the +1
proposal CONNECT BY
is outsider, just like the WHERE NOT NULL
external request.
source to share