CODEWITHSUNDEEP
regexoracle-database
Ananta
Ananta

Reputation: 700

use regex with oracle

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}+?

Upvotes: 1

Views: 110

Answers (2)

Dmitry Grekov
Dmitry Grekov

Reputation: 688

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

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37470

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

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

Demo

Upvotes: 1

Related Questions