oracle, extract multiple substring from a string

Question

Trying to extract two numeric values (8 and 10) from below string as two new columns.

Tried to benefit from the answer of this question but couldn't solve this one.

Any help appreciated!

'{"someInconsiderableText1": {"answer": "8", "question": "someInconsiderableText2"}, "someInconsiderableText3": {"answer": "10", "question": "someInconsiderableText4"}}'

Your question is a bit vague about what you want to do. Do you only want the numbers 8 and 10 or are you looking for any JSON value to an answer object? And what version of Oracle (newer versions have JSON parsing libraries)? — Sam M
– Sam M, Commented Sep 16, 2018 at 21:13
@ Sam M, the string I've provided above is the value of a CLOB field in the Oracle data warehouse (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production). This field includes the answers of two separate questions. What I need is to select/extract the answers to these questions as two different fields. Hope it is clearer now. In brief, I need two new fields, the first one will have the value '8' and the second one '10'. — kzmlbyrk
– kzmlbyrk, Commented Sep 16, 2018 at 21:22
Native JSON support was introduced in Oracle 12c. I've read about this third-party JSON parsing library for Oracle but never used it --> stackoverflow.com/questions/30393214/…. It's tempting to try JSON string manipulation with regex but gets complicated quickly and is error prone. You're better of trying to use a JSON library. — Sam M
– Sam M, Commented Sep 16, 2018 at 21:27

Littlefoot · Accepted Answer · 2018-09-16 21:28:49Z

1

Would this help?

SQL> with test (col) as
  2    (select '{"someInconsiderableText1": {"answer": "8", "question": "someInconsiderableText2"},
"someInconsiderableText3": {"answer": "10", "question": "someInconsiderableText4"}}'
  3     from dual
  4    )
  5  select replace(regexp_substr(col, '"\d+"', 1, 1), '"') first,
  6         replace(regexp_substr(col, '"\d+"', 1, 2), '"') second
  7  from test;

FIRST      SECOND
---------- ----------
8          10

SQL>

answered Sep 16, 2018 at 21:28

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

oracle, extract multiple substring from a string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related