Obtaining string after Last Slash in BigQuery Standard SQL

user_8947312 0
Viewed 6733

Let's say I have a column called 'Youtube' and I want to extract the string after the last slash of a URL. How would I do this in BigQuery Standard SQL?

Examples:

https://youtube.com/user/HaraldSchmidtShow

https://youtube.com/user/applesofficial

https://youtube.com/user/GrahamColton

Essentially, I want:

HaraldSchmidtShow

applesofficial

GrahamColton
google-bigquery sql regex
3 Answers

An alternative to the previous answer, which also works when there's a '/' at the end:

WITH data AS(
  SELECT 'https://youtube.com/user/HaraldSchmidtShow' AS url UNION ALL
  SELECT 'https://youtube.com/user/applesofficial' UNION ALL
  SELECT 'https://youtube.com/user/GrahamColton' UNION ALL
  SELECT 'https://youtube.com/user/GrahamColton/'
)

SELECT REGEXP_EXTRACT(url, r'/([^/]+)/?$') name
FROM `data`
user_1324380

This might already do the trick for you:

WITH data AS(
  SELECT 'https://youtube.com/user/HaraldSchmidtShow' AS url UNION ALL
  SELECT 'https://youtube.com/user/applesofficial' UNION ALL
  SELECT 'https://youtube.com/user/GrahamColton'
)

SELECT
  SPLIT(url, '/')[SAFE_OFFSET(ARRAY_LENGTH(SPLIT(url, '/')) - 1)] AS name
FROM `data`

It just splits the string and goes for the last value.

user_10022770

Below is for BigQuery Standard SQL 

#standardSQL
SELECT url, 
  (SELECT v FROM UNNEST(SPLIT(url, '/')) v WITH OFFSET o 
    WHERE v != '' ORDER BY o DESC LIMIT 1
  ) last_string
FROM `data`

You can test, play with above using dummy data as 

#standardSQL
WITH data AS(
  SELECT 'https://youtube.com/user/HaraldSchmidtShow' AS url UNION ALL
  SELECT 'https://youtube.com/user/applesofficial' UNION ALL
  SELECT 'https://youtube.com/user/GrahamColton/' UNION ALL
  SELECT 'youtube.com/channel/UCEDBbJXgUqRQXCOsluJJ0FQ'
)
SELECT url, 
  (SELECT v FROM UNNEST(SPLIT(url, '/')) v WITH OFFSET o 
    WHERE v != '' ORDER BY o DESC LIMIT 1
  ) last_string
FROM `data`

with result 

Row url                                             last_string  
1   https://youtube.com/user/HaraldSchmidtShow      HaraldSchmidtShow    
2   https://youtube.com/user/applesofficial         applesofficial   
3   https://youtube.com/user/GrahamColton/          GrahamColton     
4   youtube.com/channel/UCEDBbJXgUqRQXCOsluJJ0FQ    UCEDBbJXgUqRQXCOsluJJ0FQ

Obviously, using regular expression functions as in Felipe's answer - more elegant and easier to read.
But in some cases using above approach still has practical value so I wanted to bring it to that post

user_52219440
Related