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?

Maybe REGEXP_SUBSTR(col, '([0-9.]+)"}$', 1, 1, NULL, 1) will be enough? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Oct 7, 2021 at 7:05
Or, REGEXP_SUBSTR(col, '"xyzVersion" : "([^"]+)"', 1, 1, NULL, 1) as result? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Oct 7, 2021 at 7: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
– Tobitor, Commented Oct 7, 2021 at 7:14

Wiktor Stribiżew · Accepted Answer · 2021-10-07 07:19:05Z

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

answered Oct 7, 2021 at 7:19

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barbaros Özhan · Accepted Answer · 2021-10-07 07:15:21Z

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

answered Oct 7, 2021 at 7:15

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

Tobitor Over a year ago

Thanks, but in my case this one did not work out but extracted the whole string in my column...

Barbaros Özhan Over a year ago

you're welcome @Tobitor , can you share the case for which the expression doesn't work properly ..?

Collectives™ on Stack Overflow

REGEXP_SUBSTR - how to get special characters after specific pattern?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related