Redshift / Regular Expression (Positive Lookbehind and Positive Lookahead) does not work

Question

I'm fairly new to RegEx and am trying to extract following values from key:value pairs in the following text -

Values to be extracted -

RDU5

String - "stopCode":"RDU5"

I'm using following expression - ((?<=stopCode\":\").*?(?="))

This RegEx works perfectly fine except for on Redshift as Redshift does not support Positive Lookbehind and Positive Lookahead.

Can you please suggest what would be the Redshift alternative to this? I have gone through https://docs.aws.amazon.com/redshift/latest/dg/pattern-matching-conditions-posix.html but am unable to modify it to work on Redshift.

Any suggestions would be much appreciated.

Thanks!

Answer 1

Assuming you want the key that goes with the value, you want something like

/"([^"]*)":"RDU5"/

but really if your data are JSON then you should use a JSON parser to analyse them.