CODEWITHSUNDEEP
regexoracle-database
Snipe656
Snipe656

Reputation: 845

Oracle using REGEXP_SUBSTR to return values within brackets

We store some application parameters in our database and I am looking for a method via SQL to extract/parse the parameters out. An example of this data would be the following:

PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]

So for example the value for Projection would be "Transverse" and quotes would be part of that. I figured the easiest approach to getting this out would be using the REGEXP_SUBSTR function. This is my current attempt:

SELECT REGEXP_SUBSTR('PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]', 'PROJECTION\[[^][]*\]') FROM dual;

That though returns:

PROJECTION["Transverse"]

I realize that I could then just strip out the unneeded characters but I was curious if someone could recommend any REGEX tweaks so that the SELECT query instead returns simply "Transverse" for this example. Projection is not always what I'd want to pull out though, for example if I wanted Unit then I'd need returned: "Two",2

This is using Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

Upvotes: 0

Views: 3700

Answers (3)

guest
guest

Reputation: 1

SELECT REGEXP_SUBSTR('PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]', 'PROJECTION\[([^][]*)\]', 1, 1, null, 1) FROM dual;

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

You can use backreferences (\1, \2, etc.) for this. Basically you want to match three parts of the string:

  1. Everything before the PROJECTION parameter value.
  2. The PROJECTION parameter value.
  3. Everything after the PROJECTION parameter value.

These three matching regexes have to be inside of parentheses for the backreferencing to work. The regex I came up with is this:

(.*PROJECTION\[")(.+)("].*)
                     ^^^^^^ Backref 3: everything after the PROJECTION parameter value
                 ^^^^ Backref 2: the PROJECTION parameter value
^^^^^^^^^^^^^^^^^ Backref 1: everything before the PROJECTION parameter value

Then just replace with backref 2, ignoring 1 and 3:

REGEXP_REPLACE(expr, '(.*PROJECTION\[")(.+)("].*)', '\2')

Upvotes: 0

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

SELECT REGEXP_REPLACE(
   'PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]',
   '^.*PROJECTION\[(.+?)\].*$', '\1'
) FROM dual

Upvotes: 1

Related Questions