pulling stringvalue

Question

I would like to have a regex function that is able to pull the xxx value => 5ed4b27908106a000ee0c501 and nothing else. Any bigquery gurus in the crowd - much much appreciated!

{'system_ID': '5ed0cd139c99000f15e70','xxx': '5ed4b27908106a000ee0c501'}}

I would really appreciate it!!

Answer 1

Below is for BigQuery Standard SQL

REGEXP_EXTRACT(str, r"'rafIn': '(.*?)'")   

So, if to apply to example str in your question

REGEXP_EXTRACT("{'system_ID': '5ed0cd139c99000f15e70','rafIn': '5ed4b27908106a000ee0c501'}}", r"'rafIn': '(.*?)'")    

result is

5ed4b27908106a000ee0c501