Extract String from URL in Bigquery

Question

I have several 1.000 URLs and want to extract some values from the URL parameters. Here some examples from the DB:

["//xxx.com/se/something?SE_{ifmobile:MB}{ifnotmobile:DT}_A_B_C_D_E_F_G_H"]
["//www.xxx.com/se/car?p_color_car=White?SE_{ifmobile:MB}{ifnotmobile:DT}_A_B_C_D_E_F_G_H"]

I want to extract the SE | A | B | C | D | E | F | G | H

I have tried it with REGEXP_EXTRACT

REGEXP_EXTRACT_ALL(Url,r'(?:\?|&)(?:([^_]+)_(?:[^&]*))') as Country

The problem is since I have two '?' the outcome returns SE for the first url, and p for the second url. How can I solve this in one regexp so that I don't get the p but actual SE for the second url as well.

Wiktor Stribiżew · Accepted Answer · 2020-09-25 09:17:00Z

1

You can use

[?&]([^_]+)_[^&?]*$

See the regex demo. Details:

[?&] - a ? or & char
([^_]+) - Group 1 (the actual output string): one or more chars other than _
_ - a _ char
[^&?]* - zero or more & or ? chars
$ - end of string.

answered Sep 25, 2020 at 9:17

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

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

2 Comments

user3052850 Over a year ago

REGEXP_EXTRACT_ALL(Url, r'[?&]([^_]+)_[^&?]*$ as URL Was this the thought? I get error about unclosed string literal.

Wiktor Stribiżew Over a year ago

@user3052850 Sure, just close it, REGEXP_EXTRACT_ALL(Url, r'[?&]([^_]+)_[^&?]*$')

Collectives™ on Stack Overflow

Extract String from URL in Bigquery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related