CODEWITHSUNDEEP
sqlgoogle-bigquery
sharkorama
sharkorama

Reputation: 75

BigQuery SQL extract string before 3rd occurance of /

I have a BQ table with URLs in one column. However I would like to extract only the domain name.

For example I have the following strings

http://u.example.com/g/foo
http://www.exmple.com/g/bar

and I want to extract everything before the 3rd occurence of '/' with the output looking like the following: 

http://u.example.com
http://www.example.com

Thanks a lot

Upvotes: 1

Views: 2392

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

Try using REGEXP_EXTRACT:

SELECT
    REGEXP_EXTRACT(col, '^(http://[^/]+)') AS url_fragment
FROM yourTable;

A more general pattern to cover different URLs might be this:

SELECT
    REGEXP_EXTRACT(col, '^(\w+://[^/]+)') AS url_fragment
FROM yourTable;

Upvotes: 2

Related Questions