CODEWITHSUNDEEP
sqlregexoracle-databaseoracle11g
Bishal
Bishal

Reputation: 63

REGEXP_SUBSTR : extracting portion of string between [ ] including []

I am on Oracle 11gR2.

I am trying to extract the text between '[' and ']' including [].

ex:

    select regexp_substr('select userid,username from tablename where user_id=[REQ.UID] and username=[REQD.VP.UNAME]','\[(.*)\]') from dual

Output:

    [REQ.UID] and username=[REQD.VP.UNAME]

Output needed:

    [REQ.UID][REQD.VP.UNAME]

Please let me know how to get the needed output.

Thanks & Regards, Bishal

Upvotes: 1

Views: 5972

Answers (2)

John Doyle
John Doyle

Reputation: 7803

Assuming you are just going to have two occurrences of [] then the following should suffice. The ? in the .*? means that it is non-greedy so that it doesn't gobble up the last ].

select
 regexp_replace('select userid,username from tablename where user_id=[REQ.UID] and username=[REQD.VP.UNAME]'
,'.*(\[.*?\]).*(\[.*?\]).*','\1\2')
from dual
;

Upvotes: 2

latkin
latkin

Reputation: 16812

I'm not an Oracle user, but from quick perusal of the docs, I think this should be close:

REGEXP_REPLACE('select userid,username from tablename where user_id=[REQ.UID] and username=[REQD.VP.UNAME]',
               '^[^\[]*(\[[^\]]*\])[^\[]*(\[[^\]]*\])$', '\1 \2')

Which looks much nastier than it is.

Pattern is:

  • ^[^\[]* Capture all characters up to (but not including) the first [
  • (\[[^\]]*\]) Capture into group 1 anything like [<not "]">]
  • [^\[]* Capture everything up to (nut not including) the next [
  • (\[[^\]]*\]) Capture into group 2 anything like [<not "]">], at the end of the string

Then the replacement is simple, just <grp 1> <grp 2>

Upvotes: 1

Related Questions