BigQuery REGEXP_EXTRACT from URL - extract parameters values

Question

I need to use REGEXP_EXTRACT on various URLs i have in BigQuery and extract different strings from them.

For exmaple, i have this URL:

url = https://www.whatever.com/record-a-beautiful-and-professional-voice-over?sec_context=recommendation&context_alg=nodes&sec_context_referrer=search

I want to use the BigQuery REGEXP_EXTRACT function and extract the string that comes after the parameter named context_alg= (presented after the first & in the URL). Meaning - my output will be nodes.

(context_alg is a parameter in the URL and always has the same name)

So actually I need to use something like:

REGEXP_EXTRACT(url, "REGEXP that bring back 'nodes')

Thank you !

your question is not clear enough... please clarify with proper input and exact output you want... if you cant explain better then give two set of intput and two set of output respectively — Mustofa Rizwan
– Mustofa Rizwan, Commented Oct 18, 2016 at 9:50

Hirnhamster · Accepted Answer · 2018-04-08 15:20:15Z

4

If you need to extract all parameters from a URL, you can also use REGEXP_EXTRACT_ALL as follows:

REGEXP_EXTRACT_ALL(query,r'(?:\?|&)((?:[^=]+)=(?:[^&]*))') as params

This will return the result as an array (see How to extract URL parameters as ARRAY in Google BigQuery):

answered Apr 8, 2018 at 15:20

Hirnhamster

7,51910 gold badges47 silver badges77 bronze badges

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

1 Comment

FKrauss Over a year ago

Thanks a lot for sharing this code. just adding one thing to save some ppl's time: If you want to grab a particular parameter's value,. use this regex expression r'(?:\?|&)(?:(?:[myparameter=]+)=([^&]*))'

Mikhail Berlyant · Accepted Answer · 2017-04-25 19:21:17Z

3

try below for BigQuery

REGEXP_EXTRACT(url, r'context_alg=([^?&#]*)')

answered Apr 25, 2017 at 19:21

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Collectives™ on Stack Overflow

BigQuery REGEXP_EXTRACT from URL - extract parameters values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related