My DB Version - 10.2.0.4.0
I have a string like this
with t
as
(
select q'['My column',LPAD(TRIM(my_column),4,'0'),10,10000]' str
from dual
)
select * from t
I am looking for a SQL solution that will convert this string into row like this
'My column'
LPAD(TRIM(my_column),4,'0')
10
10000
Normal way to convert delimited string to row would be like this
with t
as
(
select q'['My column',LPAD(TRIM(my_column),4,'0'),10,10000]' str
from dual
)
select regexp_substr(str,'[^,]+',1,level) val
from t
connect by level <= length(str)-length(replace(str,','))+1
But this would result in
'My column'
LPAD(TRIM(my_column)
4
'0')
10
10000
But this is incorrect. So any idea how to solve it?