Oracle SQL substrings on three character pattern

Question

I need to find fisrt, second and third substring from string like "One - Two-Two- Three-Three" by splitting them only by " - " (with first and last characters being spaces).

I can split them by "-", like:

regexp_substr(teenimi, '[^-]+', 1, 1 ) as a,
regexp_substr(teenimi, '[^-]+', 1, 2 ) as b,
regexp_substr(teenimi, '[^-]+', 1, 3 ) as c

which gives me result like: One // Two // Two, but I need One // Two-Two // Three-Three

Wiktor Stribiżew · Accepted Answer · 2016-10-19 12:44:55Z

2

You may use a regex like

[^- ]+(-[^- ]+)*

See the regex demo

It will match

[^- ]+ - 1 or more chars other than - and space
(-[^- ]+)* - zero or more sequences of
- - - a hyphen
- [^- ]+ - see above.

Basically, it will match - that has no space on either side and non-whitespace characters.

answered Oct 19, 2016 at 12:44

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

Oracle SQL substrings on three character pattern

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related