Split the given column in oracle sql

Question

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

Answer 1

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