我的字符串如下'{ "city": "(not set)", "cityId": "9108665", "continent": "Americas", "country": "United States", "latitude": "0.0000", "longitude": "0.0000", "metro": "(not set)", "networkDomain": "(not set)", "networkLocation": "(not set)", "region": "Pennsylvania", "subContinent": "Northern America" }'
我如何使用REGEXP_SUBSTR
获取宾夕法尼亚州地区
我正在努力然而,select REGEXP_SUBSTR(string,':[^:]+[[:alpha:]]',1,10) as region
并没有给我想要的。
您尝试的模式:[^:]+[[:alpha:]]
匹配过多,因为被否定的字符类[^:]+
匹配除:
之外的任何字符。该匹配将一直持续到下一个键值对。
如果你只想选择宾夕法尼亚州,你可以使用更具体的模式来获得第10场比赛的捕获组值:
"[^"]*":s*"([^"]*)"
例如:
SELECT regexp_substr('{ "city": "(not set)", "cityId": "9108665", "continent": "Americas", "country": "United States", "latitude": "0.0000", "longitude": "0.0000", "metro": "(not set)", "networkDomain": "(not set)", "networkLocation": "(not set)", "region": "Pennsylvania", "subContinent": "Northern America" }', '"[^"]*":s*"([^"]*)"',1,10,NULL,1) as region from dual;
输出
REGION
Pennsylvania
查看Oracle演示
但也许向模式中添加区域以从示例数据中获得单个匹配会更容易。
"region":s*"([^"]*)"
查看另一个Oracle演示