Oracle sql Split String and extract data

Question

i have an oracle database with a column entry as the following:

{"Name":"Robert","information":"[200A1F0D00","Changed position from Dep1 to Dep2. Changed location from NewYork to Paris. Salary group switch from low to high."]}

The following result i want to generate with SQL:

ID	Dep_old	Dep_new	Loc_old	Loc_new	salary_old	salary_new
200A1F0D00	Dep1	Dep2	New York	Paris	low	high

How can I do this? I tried to use regex_substring functions, but it didn´t work out. For now: this is my progress:

select REGEXP_SUBSTR(f.EMPLOYEE, '[^,]+', 1, 2) AS a,
       REGEXP_SUBSTR(f.EMPLOYEE, '[^,]+', 1, 3) AS b,
from Staff.EMPLOYEE f;

Can you please help me and create the query? Best regards dontknowguy

MT0 · Accepted Answer · 2022-04-06 19:05:49Z

Assuming a valid JSON string (you have "[ when it should be [") then, from Oracle 12, you can use:

SELECT j.id,
       REGEXP_SUBSTR(j.info, 'Changed position from (.+?) to (.+?)\.', 1, 1, NULL, 1)
         AS dep_old,
       REGEXP_SUBSTR(j.info, 'Changed position from (.+?) to (.+?)\.', 1, 1, NULL, 2)
         AS dep_new,
       REGEXP_SUBSTR(j.info, 'Changed location from (.+?) to (.+?)\.', 1, 1, NULL, 1)
         AS loc_old,
       REGEXP_SUBSTR(j.info, 'Changed location from (.+?) to (.+?)\.', 1, 1, NULL, 2)
         AS loc_new,
       REGEXP_SUBSTR(j.info, 'Salary group switch from (.+?) to (.+?)\.', 1, 1, NULL, 1)
         AS sal_old,
       REGEXP_SUBSTR(j.info, 'Salary group switch from (.+?) to (.+?)\.', 1, 1, NULL, 2)
         AS sal_new
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         value,
         '$.information'
         COLUMNS
           id   VARCHAR2(20)  PATH '$[0]',
           info VARCHAR2(200) PATH '$[1]'
       ) j

Which, for the sample data:

CREATE TABLE table_name (value BLOB CHECK (value IS JSON));

INSERT INTO table_name (value)
VALUES ('{"Name":"Robert","information":["200A1F0D00","Changed position from Dep1 to Dep2. Changed location from NewYork to Paris. Salary group switch from low to high."]}');

Outputs:

ID DEP_OLD DEP_NEW LOC_OLD LOC_NEW SAL_OLD SAL_NEW

200A1F0D00 Dep1 Dep2 NewYork Paris low high

db<>fiddle here

Collectives™ on Stack Overflow

Oracle sql Split String and extract data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related