BigQuery Regex to extract string between two substrings

Question

From this example string:

{&q;somerandomtext&q;:{&q;Product&q;:{&q;TileID&q;:0,&q;Stockcode&q;:1234,&q;variant&q;:&q;genomics&q;,&q;available&q;:0"}

I'm trying to extract the Stockcode only.

REGEXP_REPLACE(col, r".*,&q;Stockcode&q;:/([^/$]*)\,&q;.*", r"\1")

So the result should be

1234

however my Regex still returns the entire contents.

Mikhail Berlyant · Accepted Answer · 2021-12-09 01:11:30Z

3

use regexp_extract(col, r"&q;Stockcode&q;:([^/$]*?),&q;.*")

if applied to sample data in your question - output is

answered Dec 9, 2021 at 1:11

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amazing, thanks for your help, this did the trick. Much appreciated @mikhail-berlyant