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 · Accepted Answer · 2017-04-19 19:15:11Z

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, 2017 at 19:15

answered Apr 19, 2017 at 18:34

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shakiba Moshiri Over a year ago

May your second link has a problem: online demo

Wiktor Stribiżew Over a year ago

@k-five: Typing on a snail-laptop usually results in such "typos". It should be rextester.com/QBE60576.

David Faber · Accepted Answer · 2017-04-19 19:39:44Z

1

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.

answered Apr 19, 2017 at 19:39

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related