How to split strings using two delimiter in Oracle 11g regexp_substr functions

Question

I have doubt to split a string using the delimiter.

First split based on , delimiter select those splitted strings should split based on - delimiter

My original string: UMC12I-1234,CSM3-123,VQ, Expected output:

UMC12I
CSM3
VQ

Each value comes as row value

I tried the 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

-- splitted based on , delimiter

Output is:

UMC12I-1234
CSM3-123
VQ

how can I get my expected output? (need to split again - delimiter)

Wiktor Stribiżew · Answer 1 · 2017-04-19T19:15:11.987

1

You may extract the "words" before the - with the regexp_substr using

([^,-]+)(-[^,-]+)?

The pattern will match and capture into Group 1 one or more chars other than , and -, then will match an optional sequence of - and 1+ chars other than ,and -.

See the regex demo.

Use this regex_substr line instead of yours with the above regex:

SELECT trim(regexp_substr('UMC12I-1234,CSM3-123,VQ,', '([^,-]+)(-[^,-]+)?', 1, LEVEL, NULL, 1)) fab_site

See the online demo

edited Apr 19 '17 at 19:15

answered Apr 19 '17 at 18:34

Wiktor Stribiżew

607,720
39
448
563

May your second link has a problem: **online demo** – Shakiba Moshiri Apr 19 '17 at 19:11
@k-five: Typing on a snail-laptop usually results in such "typos". It should be http://rextester.com/QBE60576. – Wiktor Stribiżew Apr 19 '17 at 19:15

score 1 · Answer 2 · answered Apr 19 '17 at 19:39

You might 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;

We start by matching any substring that starts either with the start of the whole string ^ or with a comma ,, the delimiter. We then get all the characters that match neither a comma nor a dash -. Once we have that substring we trim any leftover commas from it.

P.S. I think the +1 in the CONNECT BY clause is extraneous, as is the WHERE NOT NULL in the "outer" query.

How to split strings using two delimiter in Oracle 11g regexp_substr functions

2 Answers2