how to extract out a string with SYMBOLS after a pattern in a URL string in Google BigQuery

Question

i have two possible forms of a URL string

http://www.abcexample.com/landpage/?pps=[Y/lyPw==;id_1][Y/lyP2ZZYxi==;id_2];[5403;ord];
http://www.abcexample.com/landpage/?pps=Y/lyPw==;id_1;unknown;ord; 

I want to get out the Y/lyPw== in both examples

so everything before ;id_1 between the brackets

will always come after the ?pps= part

What is the best way to approach this? I want to use the big query language as this is where my data sits

Answer 1

Here is one way to build a regular expression to do it:

SELECT REGEXP_EXTRACT(url, r'\?pps=;[\[]?([^;]*);') FROM
(SELECT "http://www.abcexample.com/landpage/?pps=;[XYZXYZ;id_1][XYZZZZ;id_2];[5403;ord];" 
  AS url),
(SELECT "http://www.abcexample.com/landpage/?pps=;XYZXYZ;id_1;unknown;ord;"
  AS url)