Standard SQL: How to Extract Everything Before a Certain Character - Google BigQuery

user_15589144 0
Viewed 1294

I have a database with a Landing Page column with different URLs from a website like you can see in the image below:

Text

I want to keep the text from the beginning of the URL to the quotation mark before the non sense code at the end. How can I do this? Not sure if I can use Regex on SQL or how to do it.

P.S.: I am working with Google BigQuery and standard SQL.

Thanks!

google-bigquery sql
2 Answers

Consider below approach

select Landing_Page, regexp_extract(Landing_Page, r'^[^?]*')
from your_table

if applied to sample data in your question - output is

enter image description here

user_52219440

You can do it without regex:

select split('advice/management?sdjghwehgf', '?')[OFFSET(0)]
user_134735250
Related