CODEWITHSUNDEEP
sqlregexoracle-databaseoracle10g
sameh.q
sameh.q

Reputation: 1709

REGEXP_SUBSTR find groups

I'm not able to find the groups of my pattern, and it is really giving me a hard time.

I've went through many SO answers, and Oracle documentation also, but it seems regular expressions is my weak point.

Basically, what I want is to get the text between $E[ ]# and $D[ ]# I've wrote the pattern ^(\$E\[([A-Z|a-z|0-9|_]+)\]#)((\$D\[(.)*\]#)?)$ which validates the entire string successfully, but I'm not able to find the groups in that pattern

I've tried to following query:

  SELECT 
        REGEXP_SUBSTR('$E[some_key]#$D[some value]#',pattern, 1) AS MATCH1, 
        REGEXP_SUBSTR('$E[some_key]#$D[some value]#',pattern, 2) AS MATCH2
  FROM DUAL;

I've tried many variations of the 4th parameter of the function REGEXP_SUBSTR('some text','pattern above', 1) and its getting me the complete string, also tried REGEXP_SUBSTR('some text','pattern above', 1, 1), but got the same results, either the full string or NULL.

Upvotes: 2

Views: 3912

Answers (2)

Jens Krogsboell
Jens Krogsboell

Reputation: 1123

Try this pattern (only 11g):

\$(D|E)\[([^]]*)

with this query:

SELECT 
    REGEXP_SUBSTR('$E[some_key]#$D[some value]#',pattern, 1, 1, null, 2) AS MATCH1, 
    REGEXP_SUBSTR('$E[some_key]#$D[some value]#',pattern, 1, 2, null, 2) AS MATCH2
FROM DUAL;

This will get you the "first occurrence of the second sub expression" and the "second occurrence of the second sub expression" where "second sub expression" refers to what matches the second set of parentheses in the pattern.

Upvotes: 1

Rimas
Rimas

Reputation: 6024

Modified pattern:

^(\$E\[([A-Z|a-z|0-9|_]+)\]#)(\$D\[(.*)\]#)?$

Use REGEXP_REPLACE with back reference (works on Oracle 10g):

SELECT 
  REGEXP_REPLACE('$E[some_key]#$D[some value]#',pattern, '\2') AS MATCH1, 
  REGEXP_REPLACE('$E[some_key]#$D[some value]#',pattern, '\4') AS MATCH2
FROM DUAL;

On Oracle 11g you can do:

SELECT 
  REGEXP_SUBSTR('$E[some_key]#$D[some value]#',pattern, 1,1,null,2) AS MATCH1, 
  REGEXP_SUBSTR('$E[some_key]#$D[some value]#',pattern, 1,1,null,4) AS MATCH2
FROM DUAL;

Documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions138.htm

Upvotes: 2

Related Questions