CODEWITHSUNDEEP
google-bigquery
Serge
Serge

Reputation: 57

How to remove all characters after a certain character in BigQuery using standard SQL?

I have a list of URLs/ Here is an example — www.site.com/product/item1/?utm_source=google&utm_medium=cpc

How I can get all characters before question mark using BigQuery? Sо I want to get www.site.com/product/item1/ from this string.

Thanks a lot!

Upvotes: 4

Views: 12277

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

The easiest way I think is to use SPLIT function as in below example

SPLIT(url, '?')[OFFSET(0)]

As alternative, you can use REGEXP_EXTRACT as in below example

REGEXP_EXTRACT(url, r'[^?]*')

Upvotes: 9

Nathan Nasser
Nathan Nasser

Reputation: 1004

you can use the REGEXP_EXTRACT function. You will have to create the regexp expression though.

Furthermore, you could use Dataflow to transform the data as another option.

Upvotes: 1

Related Questions