CODEWITHSUNDEEP
sqloracle-databaseoracle12c
LochanaT
LochanaT

Reputation: 17

Split the given column in oracle sql

I want to split the following table

column
{"senderName":"John David", "senderCountry":"LKA", "senderAddress":"No 230,ABS,11200}

I want to get following table using oracle sql

senderName senderCountry senderAddress
John David LKA No 230,ABS,11200

I tried the following code

SELECT
regexp_substr(column,'[^:]+', 1, 1) As senderName,
regexp_substr(column,'[^:]+', 1, 2) As senderCountry,
regexp_substr(column,'[^:]+', 1, 3) As senderAddress  
From table

But I got the following table

senderName senderCountry senderAddress
"senderName" "John David", "senderCountry" "LKA", "senderAddress"

Anyone can help me?

Thank you

Upvotes: 0

Views: 90

Answers (3)

MT0
MT0

Reputation: 167972

You should use JSON_TABLE for this.

If you cannot and your JSON is very simple (i.e. the keys are only going to appear once and you do not have a complicated path to parse) then you can use regular expressions (but don't if you can use a proper JSON parser like JSON_TABLE):

SELECT REPLACE(
         REGEXP_SUBSTR(
           column_name,
           '"senderName":\s*"((\\"|[^"])*)"',
           1,
           1,
           NULL,
           1
         ),
         '\"',
         '"'
       ) AS senderName,
       REPLACE(
         REGEXP_SUBSTR(
           column_name,
           '"senderCountry":\s*"((\\"|[^"])*)"',
           1,
           1,
           NULL,
           1
         ),
         '\"',
         '"'
       ) AS senderCountry,
       REPLACE(
         REGEXP_SUBSTR(
           column_name,
           '"senderAddress":\s*"((\\"|[^"])*)"',
           1,
           1,
           NULL,
           1
         ),
         '\"',
         '"'
       ) AS senderAddress  
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (column_name CHECK (column_name iS JSON)) AS
SELECT '{"senderName":"John David", "senderCountry":"LKA", "senderAddress":"No 230,ABS,11200"}' FROM DUAL UNION ALL
SELECT '{"senderName":"Jane Smith", "senderAddress":"No 42,\"Home\", XYZ, 98765", "senderCountry":"ABC"}' FROM DUAL;

Note: Your JSON was invalid as it is missing a closing ".

Outputs:

SENDERNAME SENDERCOUNTRY SENDERADDRESS
John David LKA No 230,ABS,11200
Jane Smith ABC No 42,"Home", XYZ, 98765

db<>fiddle here

Upvotes: 1

nayi224
nayi224

Reputation: 565

It’s easier to split with "

with tab1 as (
select '{"senderName":"John David", "senderCountry":"LKA", "senderAddress":"No 230,ABS,11200"}' col from dual
)
select replace(regexp_substr(t1.col, '"[^"]+"',1, 2), '"', ''),
       replace(regexp_substr(t1.col, '"[^"]+"',1, 4), '"', ''),
       replace(regexp_substr(t1.col, '"[^"]+"',1, 6), '"', '')
  from tab1 t1

Upvotes: 0

Del
Del

Reputation: 1599

If you are using 18c or greater, you can just use JSON_TABLE:

WITH test_data (json) AS
(
  SELECT '{"senderName":"John David", "senderCountry":"LKA", "senderAddress":"No 230,ABS,11200"}' FROM DUAL
)
SELECT jt.*
FROM test_data td,
JSON_TABLE(td.json,
           '$'
           COLUMNS (senderName VARCHAR2(100) PATH '$.senderName',
                    senderCountry VARCHAR2(100) PATH '$.senderCountry',
                    senderAddress VARCHAR2(100) PATH '$.senderAddress')) jt

This produces the following results:

SENDERNAME SENDERCOUNTRY SENDERADDRESS
John David LKA No 230,ABS,11200

Here is a DBFiddle showing this working (Link)

Upvotes: 1

Related Questions