长字符串的字段数据分类提取的方法

一个字段里的数据如下：

[{"name":"地点","itemType":"input","itemId ":"217474","itemValue":"爵士鞋行门头上方"},{"name":"图片","itemType":"image","itemId ":"898808","itemValue":["https://aa.com/uploadfile/Pictures/2022/3/28/382c66a75a614768a48670697f85a132.jpg","https://aa.com/uploadfile/Pictures/2022/3/28/57cac58516714409a38bd1068eaea480.jpg"]},{"name":"整改时间","itemType":"time","itemId":"124470","itemValue":"2022/03/28 16:25"}]

用FR如何处理才能提取出相应的数据？我主要是想提取https:后面哪两个网址，但这个网址是不固定个数的，可能是1个也可能是3个4个。

FineReport 章鱼哥48030 发布于 2022-5-16 10:38

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：5 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共3回答

最佳回答

CD20160914Lv8专家互助
发布于2022-5-16 12:10（编辑于 2022-5-16 15:52）

假如你上面的这部分数据在A1单元格，公式如下，然后再设置单元格为横向扩展

MAPARRAY(MAPARRAY(GREPARRAY(split(A1, ","), find("https:", item) > 0),mid(item,find("https",item),1000)),mid(item,1,find(".jpg",item)+3))

如果你是oracel，那么可以这样

select

REGEXP_SUBSTR(t2.str,'http(s://)\w+\.+\w.+\.jpg') as 截取后

from (

select

NVL(REGEXP_SUBSTR(t.tstra, '[^,]+', 1, LEVEL, 'i'), '无') AS STR

from (

select '[{"name":"地点","itemType":"input","itemId ":"217474","itemValue":"爵士鞋行门头上方"},{"name":"图片","itemType":"image","itemId ":"898808","itemValue":["https://aa.com/uploadfile/Pictures/2022/3/28/382c66a75a614768a48670697f85a132.jpg","https://aa.com/uploadfile/Pictures/2022/3/28/57cac58516714409a38bd1068eaea480.jpg"]},{"name":"整改时间","itemType":"time","itemId":"124470","itemValue":"2022/03/28 16:25"}]' as tstra from dual

) t

CONNECT BY LEVEL <=

LENGTH(t.tstra) - LENGTH(REGEXP_REPLACE(t.tstra, ',', ''))+1

) t2

where regexp_like(t2.str,'https.*')