I have such a column:

{"abcVersion" : "1.2.3.4", https://klmno.com:5678/def", "xyzVersion" : "6.7.8.9"}

I now would like to get the numbers and . after the pattern xyzVersion" : " in order to get 6.7.8.9 as result. I tried this:

REGEXP_SUBSTR(column, '\d [^a-z"] ') as result

Which obviously gives back 1.2.3.4. I do not want to specify the position with the arguments within the brackets but want to get the result specifically after the pattern mentioned above.

How could I do this?

CodePudding user response：

You can use

REGEXP_SUBSTR(col, '"xyzVersion" : "([^"] )"', 1, 1, NULL, 1) as result

Notes:

• "xyzVersion" : "([^"] )" matches "xyzVersion" : ", then captures one or more chars other than " into Group 1 and then matches a "
• The last 1 argument tells REGEXP_SUBSTR to only return the capturing group 1 value (the first 1 is the position to start searching from and the second 1 tells to match the first occurrence).

CodePudding user response：

One option would be using REGEXP_REPLACE()(TO_CHAR() conversion might be added in order to convert CLOB to an ordinary string) such as

SELECT TO_CHAR(REGEXP_REPLACE(col,'(.*xyzVersion" : )"(.*)("})','\2')) AS result
  FROM t     

RESULT
-------
6.7.8.9

Demo