Split an array of strings in oracle sql

Question

I want to use regex.substr in Oracle SQL to split a column value into multiple columns. This olumn contains strings in two formats

a) array of strings eg: ["abc","def"]

b) single string eg "abc"

I am using

regexp_substr(col1, '[^",]+', 1, 1) as val1,
regexp_substr(col1, '[^",]+', 1, 2) as val2,
regexp_substr(col1, '[^",]+', 1, 3) as val3,
regexp_substr(col1, '[^",]+', 1, 4) as val4,

Using above, I was only able to split format "b)"
For format b) I get the below result

val1   val2   val3   val4
  [     abc   def     ]

I do not want square brackets to be included in the result. Please suggest on how to proceed with this. Thanks in advance!

Answer 1

regexp_substr(col1, '[^",]+', 1, 1)

It will only return the NON-NULL values from the array. There could be NULL values in the array, so the below query would return the exact position of the elements from the array considering the NULL values:

with data as 
(
  select '["abc","def","xyz","klm","nop","qrs"]' arr from dual union all
  select '["abc",,"xyz","klm","nop","qrs"]' arr from dual union all
  select '["abc","def",,"lkj",]' arr from dual
)
select 
    regexp_substr(arr, '(.*?)(,|$)', 1, 1, NULL, 1) col1,
    regexp_substr(arr, '(.*?)(,|$)', 1, 2, NULL, 1) col2,
    regexp_substr(arr, '(.*?)(,|$)', 1, 3, NULL, 1) col3,
    regexp_substr(arr, '(.*?)(,|$)', 1, 4, NULL, 1) col4,
    regexp_substr(arr, '(.*?)(,|$)', 1, 5, NULL, 1) col5,
    regexp_substr(arr, '(.*?)(,|$)', 1, 6, NULL, 1) col6
from 
(
 select replace(replace(replace(arr, '['), '"'), ']') arr from data
);

COL1 COL2 COL3 COL4 COL5 COL6
---- ---- ---- ---- ---- ----
abc  def  xyz  klm  nop  qrs 
abc       xyz  klm  nop  qrs 
abc  def       lkj