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, Sep 16 '18 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, Sep 16 '18 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 --> https://stackoverflow.com/questions/30393214/support-for-json-in-oracle-11g. 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, Sep 16 '18 at 21:27

score 1 · Accepted Answer · answered Sep 16 '18 at 21:28

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>

oracle, extract multiple substring from a string

1 Answers1