how can I extract the key word following by a word and preceding by a comma using REGEXP_SUBSTR

Question

my string is like this '{ "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" }'

how can i use REGEXP_SUBSTR to get the region, Pennsylvania

i am trying select REGEXP_SUBSTR(string,':[^:]+[[:alpha:]]',1,10) as region however, it doesn't give me what i want.

Answer 1

The pattern :[^:]+[[:alpha:]] that you tried matches too much, as the negated character class [^:]+ matches any char except : That match will reach until the next key value pair.

If you just want to select Pennsylvania, you can use a bit more specific pattern to get the capture group value from the 10th match:

"[^"]*":\s*"([^"]*)"

For example:

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;

Output

REGION
Pennsylvania

See an Oracle demo

But perhaps it might be easier to add region to the pattern to get a single match from the example data.

"region":\s*"([^"]*)"

See another Oracle demo