json字符串的内容如下:
[{"stockName":"阳光照明","stockProfit":"5500.0000","stockCode":"600261"},{"stockName":"京 运 通","stockProfit":"6664.5000","stockCode":"601908"}]
如果需要提取出json里的前3个stockName,可以通过regexp_substr函数实现。如下:
select
substr(regexp_substr(f1, '"stockName":"[\w|\s]+', 1, 1), 14) as stockName1,
substr(regexp_substr(f1, '"stockName":"[\w|\s]+', 1, 2), 14) as stockName2,
substr(regexp_substr(f1, '"stockName":"[\w|\s]+', 1, 3), 14) as stockName3
如果要提取所有的字段,测试如下:
with temp as(
select '[{"stockName":"阳光照明","stockProfit":"5500.1","stockCode":"600261"},{"stockName":"京 运 通","stockProfit":"6664.5000","stockCode":"601908"}]' as result
)
select
substr(regexp_substr(result, '"stockName":"[\w|\s]+', 1, 1), 14) as stockName,
substr(regexp_substr(result, '"stockProfit":"[\w|\s|\.]+', 1, 1), 16) as stockProfit,
substr(regexp_substr(result, '"stockCode":"[\w|\s]+', 1, 1), 14) as stockCode
from temp
union all
select
substr(regexp_substr(result, '"stockName":"[\w|\s]+', 1, 2), 14) as stockName,
substr(regexp_substr(result, '"stockProfit":"[\w|\s|\.]+', 1, 2), 16) as stockProfit,
substr(regexp_substr(result, '"stockCode":"[\w|\s]+', 1, 2), 14) as stockCode
from temp
语法:
REGEXP_SUBSTR( string, pattern [, position [, occurrence [, regexp_modifier... [, captured_subexp ] ] ] ]) 其中,参数occurrence非常关键,当正则表达式匹配出多个子字符串时,occurrence参数表示返回第几个子字符串。
转载于: