2

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?

Tobitor
  • 1,388
  • 1
  • 23
  • 58
  • is that a string or json? – Ghost Ops Oct 07 '21 at 06:58
  • Datatype is CLOB. – Tobitor Oct 07 '21 at 06:59
  • Maybe `REGEXP_SUBSTR(col, '([0-9.]+)"}$', 1, 1, NULL, 1) ` will be enough? – Wiktor Stribiżew Oct 07 '21 at 07:05
  • 1
    Or, `REGEXP_SUBSTR(col, '"xyzVersion" : "([^"]+)"', 1, 1, NULL, 1) as result`? – Wiktor Stribiżew Oct 07 '21 at 07:07
  • The second one works out. Thanks a lot! Maybe you could also answer my question and maybe state what the arguments in your statement mean? :-) – Tobitor Oct 07 '21 at 07:14
  • You didn't answer Ghost Ops's question. Is that string supposed to be JSON? The data type is CLOB, fine; that is not what you were asked. If it is a JSON (it most definitely is in the example you gave), you should use JSON-specific functions, not regular expression functions. Unless your Oracle version is too old (11.2 or older) - JSON support was added in Oracle 12.1. So, also - what is your Oracle version? –  Oct 07 '21 at 14:10

2 Answers2

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).
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
0

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

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55