How do I get the string before the second last /

Question

I guess the answer to this question if somewhere but I wasn't able to find it.

so I have the basic following url https://mywebsite.com/path1/path2/xxxx

how can I extract path2 with regex?

thanks

Answer 1

how can I extract path2 with regex?

Below quick example for BigQuery Standard SQL - one with regex and second w/o regex

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'https://mywebsite.com/path1/path2/xxxx' url
)
SELECT 
  REVERSE(SPLIT(REVERSE(url), '/')[SAFE_OFFSET(1)]) option1,
  REGEXP_EXTRACT(url, r'(\w+)/\w+$') option2
FROM `project.dataset.table`   

with result

Row option1 option2  
1   path2   path2