Converting Array inside a String into a list of items with PL/SQL

Question

I'm receiving from an API a JavaScript array inside a string like the following:

'["qwe","asd","zxc","345"]'

And I'm trying to get it to become a list of items, which I did.

SELECT  regexp_substr(
          REPLACE( 
            REPLACE(
              REPLACE('["qwe","asd","zxc","345"]', '"', ''),
            '[',''),
          ']',''),
        '[^,]+', 1, LEVEL) AS Lista
FROM  dual
CONNECT BY instr(REPLACE( 
            REPLACE(
              REPLACE('["qwe","asd","zxc","345"]', '"', ''),
            '[',''),
          ']','')
          , ',', 1, LEVEL - 1) > 0;

The problems is that it doesn't feel right.

Please, can someone help me to either optimize/improve the code or maybe confirm that this is it and there is nothing to be done.

Thx

With supported (modern) Oracle Versions you can use json_table() — user330315
– user330315, Commented Dec 9, 2019 at 14:33

Chris Saxon · Accepted Answer · 2019-12-09 14:34:58Z

1

Assuming you're on 12.1.0.2 or higher, the "correct" way to do this is with json_table

select * from json_table (
  '["qwe","asd","zxc","345"]', '$[*]'
  columns (
    val path '$'
  )
);

VAL   
qwe    
asd    
zxc    
345

answered Dec 9, 2019 at 14:34

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paulo Künzel Over a year ago

Yes, awesome Chris, I knew there was a better way. Thx

Collectives™ on Stack Overflow

Converting Array inside a String into a list of items with PL/SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related