REGEXP_SUBSTR - how to get special characters after specific pattern?

Question

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?

Answer 1

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).