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 |
|
talhaparvaiz@yahoo.com
Messages: 14 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 |
|
Michel Cadot
Messages: 68665 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
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 08:19:08 CDT 2024
|