use regex with oracle

Question

I'm relatively new to the regex with oracle and I was wondering how to split a text beetween two pipe. Here is the input string : 1||01|SOME_TEXT||02|OTHER_TEXT||

What I want is to retrieve 01|SOME_TEXT and 02|OTHER_TEXT

Here is what I tried :

`Select regexp_substr('1||01|SOME_TEXT||02|OTHER_TEXT||', '[^\|\|]', 1, Level) From Dual
Connect By regexp_substr('1||01|SOME_TEXT||02|OTHER_TEXT||', '[^\|\|]', 1, Level) Is Not Null`

I don't know how to filter only strings that are between double pipe AND maybe containing some single pipe.

Any help are welcome.

UPDATE

I have made it with the following pattern : (\w)+\|(\w)+(\|){2,2}+?

HINT: if you want to match a specif string/character a specific number of times, you can pass {n,n} where n is any number, like [A]{3,3} will match AAA — Sudipta Mondal
– Sudipta Mondal, Commented Aug 9, 2018 at 12:53
I have tried your solution. [a-zA-Z0-9_\|]+(\|){2,2}+? But I would like it to be non greedy but I don't know how to do it — Ananta
– Ananta, Commented Aug 9, 2018 at 13:11
It might look like a cheating, but try replacing double pipes with some other character and then use it as a delimiter. — Dmitry Grekov
– Dmitry Grekov, Commented Aug 9, 2018 at 13:30

Dmitry Grekov · Accepted Answer · 2018-08-09 13:42:22Z

2

Like this?

Select regexp_substr('1||01|SOME_TEXT||02|OTHER_TEXT||', '[^|]+\|[^|]+', 1, Level) From Dual
Connect By regexp_substr('1||01|SOME_TEXT||02|OTHER_TEXT||', '[^|]+\|[^|]+', 1, Level) Is Not Null

I've kept your original query but slightly changed the pattern.

My pattern [^|]+\|[^|]+ contains three parts:

[^|]+ - a string without pipes
\| - a single pipe
[^|]+ - another string without pipes

edited Aug 9, 2018 at 13:42

answered Aug 9, 2018 at 13:37

Dmitry Grekov

6883 silver badges12 bronze badges

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

Comments

Michał Turczyn · Accepted Answer · 2018-08-09 13:18:33Z

1

Try this pattern: (?<=\|{2}).+?(?=\|{2}).

Details: it uses lookbehind (?<=\|{2}) and lookahead (?=\|{2}) to capture everything between double pipe \|{2}.

Demo

answered Aug 9, 2018 at 13:18

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

2 Comments

Ananta Over a year ago

I don't know why but it does not work with Oracle. I have made it with the following pattern : (\w)+\|(\w)+(\|){2,2}+? but don't hesitate if you have a better solution. Thanks

kfinity Over a year ago

Oracle only supports basic grouping () and backreferences. docs.oracle.com/cd/B19306_01/B14251_01/…

Collectives™ on Stack Overflow

use regex with oracle

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related