Home » SQL & PL/SQL » SQL & PL/SQL » REgexp_substr function to extract sub string
REgexp_substr function to extract sub string [message #682224] Sun, 11 October 2020 00:38 Go to next message
talhaparvaiz@yahoo.com
Messages: 10
Registered: October 2020
Junior Member
Hi, I have an oracle DB table that has a field (CLOB) that reads something like this


{"team_ID":"123","parent_id": "1234","baseline_end":"16014240000000","baseline_start":"76014230000000"}


I want to extract baseline_end and baseline_start values so that my end result would read something like this:


baseline_end baseline_start
16014240000000 76014230000000


I think we can do the above using REGEXP_SUBSTR function. but I am struggling to achieve the requirements

it should be as simple as when you see baseline_end then from 4th position, extract 14 characters. I tried but it's not working for me.

Please assist

Thanks
Re: REgexp_substr function to extract sub string [message #682225 is a reply to message #682224] Sun, 11 October 2020 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 67545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.

Before 10g (assuming the order is fixed, there are always " and never spaces between values):
SQL> col baseline_end  format a14
SQL> col baseline_start format a14
SQL> with
  2    data as (
  3      select '{"team_ID":"123","parent_id": "1234","baseline_end":"16014240000000","baseline_start":"76014230000000"}' val
  4      from dual
  5    )
  6  select substr(val,
  7                instr(val,'"baseline_end":')+16,
  8                instr(val,',"baseline_start"')-instr(val,'"baseline_end":')-17)
  9           baseline_end,
 10         substr(val,
 11                instr(val,'"baseline_start":')+18,
 12                length(val)-instr(val,'"baseline_start":')-19)
 13           baseline_start
 14  from data
 15  /
BASELINE_END   BASELINE_START
-------------- --------------
16014240000000 76014230000000
From 10g (assuming the order is fixed but " and spaces may or not appeared):
SQL> with
  2    data as (
  3      select '{"team_ID":"123","parent_id": "1234","baseline_end":"16014240000000","baseline_start":"76014230000000"}' val
  4      from dual
  5    )
  6  select regexp_substr(val, '"?([^"]+)"? *[:,}]', 1, 6, null, 1) baseline_end,
  7         regexp_substr(val, '"?([^"]+)"? *[:,}]', 1, 8, null, 1) baseline_start
  8  from data
  9  /
BASELINE_END   BASELINE_START
-------------- --------------
16014240000000 76014230000000
Starting from 12c (as your data are JSON):
SQL> with
  2    data as (
  3      select '{"team_ID":"123","parent_id": "1234","baseline_end":"16014240000000","baseline_start":"76014230000000"}' val
  4      from dual
  5    )
  6  select json_value(val,'$.baseline_end') baseline_end,
  7         json_value(val,'$.baseline_start') baseline_start
  8  from data
  9  /
BASELINE_END   BASELINE_START
-------------- --------------
16014240000000 76014230000000

[Updated on: Sun, 11 October 2020 03:23]

Report message to a moderator

Re: REgexp_substr function to extract sub string [message #682231 is a reply to message #682225] Sun, 11 October 2020 14:50 Go to previous message
talhaparvaiz@yahoo.com
Messages: 10
Registered: October 2020
Junior Member
I used the Json query and it worked.. Thanks for your assistance.
Previous Topic: RANK issue in SQL
Next Topic: Matrix Report Out put
Goto Forum:
  


Current Time: Sat Nov 28 11:24:33 CST 2020