Extract parameter from url in Google BigQuery using regex

Question

May I ask your help in order to build a regular expression to be used on Google Big Query using REGEXP_EXTRACT that will parse the value of an url parameter identified by a specific key?

Let's suppose that the parameter I would like to parse has key value equal to "source". The parsing should:

Ensure that before the key there is "?" or a "&" and after there is a "=" : so in the example match "?source=" or "&source="
Capture the value until the first "&" or end of string
In case above conditions matches more than one time it should take the value of first occurance

Here are some example of desired behaviour (they should all provide "google" as output):

www.google.com?source=google&medium=cpc --> output: google
www.google.com?source=google --> output: google
www.google.com?medium=cpc&source=google --> output: google
www.google.com?medium=cpc&source=google&keyword=foo --> output: google
www.google.com?medium=cpc&source=google&keyword=foo&source=bing --> output: google
www.google.it?medium=cpc?source=goo-gle --> output: goo-gle
www.google.it?medium=cpc?source=google?med=cpc&keyword=foo --> output: google?med=cpc

Thanks very much for any help!

Whothehellisthat · Accepted Answer · 2016-11-23 20:08:21Z

10

[?&]source=([^&]+)

The first captured group in the match will be the value of the "source" parameter.

[?&] Either ? or &
source= Literal text
([^&]+) A captured group containing 1 or more characters that are not &

edited Nov 23, 2016 at 20:08

answered Nov 23, 2016 at 19:35

Whothehellisthat

2,1521 gold badge15 silver badges15 bronze badges

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

3 Comments

Jonk Over a year ago

Thanks a lot this works on all examples! May I ask you for my understanding what would be the difference of just using REGEXP_EXTRACT(url, r'[?&]source=([^&]+)') ? It seems that this works fine also in all my examples, but I guess there must be some scenario where it would perform differently? Can you provide it to me so to fully understand? Thanks! Marco

Whothehellisthat Over a year ago

Sorry--not sure what the question is. In your case, the \r\n is probably optional. Does that help?

Whothehellisthat Over a year ago

Those were added while I was testing and I forgot to take them out ;P

Hirnhamster · Accepted Answer · 2018-04-08 15:25:06Z

6

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

(Posting here because this question ranks highly on Google for "bigquery parse url query string", but the chosen answer only works for one parameter that is already defined).

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:25

Hirnhamster

7,51910 gold badges47 silver badges77 bronze badges

1 Comment

allan.simon Over a year ago

maybe not directly related, but how would you do for this to be done once and for all ? as I imagine it's not the most efficient things to re-run the regexp on all logs every single time you query your data ?

John Williams · Accepted Answer · 2023-03-22 08:38:31Z

0

The value of source can be extracted as follows:

select regexp_extract("www.google.it?medium=cpc&source=google&keyword=foo&source=bing", "[?&]source=([^&]+)")

edited Mar 22, 2023 at 8:38

John Williams

5,8132 gold badges12 silver badges27 bronze badges

answered Mar 21, 2023 at 22:44

Metin

111 silver badge3 bronze badges

1 Comment

user67275 Over a year ago

Please add some explanation rather than posting only code.

Collectives™ on Stack Overflow

Extract parameter from url in Google BigQuery using regex

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related