sql - 在oracle sql中拆分字符串数组
问题描述
我想在 Oracle SQL 中使用 regex.substr 将列值拆分为多个列。此列包含两种格式的字符串
a) 字符串数组,例如:["abc","def"]
b) 单个字符串,例如“abc”
我在用
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,
使用上面,我只能拆分格式“b)”
对于格式b)我得到以下结果
val1 val2 val3 val4
[ abc def ]
我不希望方括号包含在结果中。请建议如何进行此操作。提前致谢!
解决方案
regexp_substr(col1, '[^",]+', 1, 1)
它只会返回NON-NULL
数组中的值。数组中可能有NULL
值,因此下面的查询将返回数组中元素的确切位置,考虑 NULL 值:
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