CODEWITHSUNDEEP
google-bigquery
Doppia L
Doppia L

Reputation: 141

Remove last x characters until a specific character

I got this string /uk-en/contact-us/frequently-asked-questions/your-trip/there-wi-fi-access-in-the-eurostar-terminals-and-board-your-trains and I need to get just the last part of the URL (until the last /). Then I want to replace '-' with a space. The strings are not with the same number of characters.

How can I do?

Thank you!

Upvotes: 2

Views: 5519

Answers (2)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

Solution using BigQuery functions:

select regexp_replace(last(split(x, "/")), "-", " ") from
(select 
  "/uk-en/contact-us/frequently-asked-questions/your-trip/there-wi-fi-access-in-the-eurostar-terminals-and-board-your-trains"
  as x)

Upvotes: 2

akhil vangala
akhil vangala

Reputation: 1053

Here is what I tried in SQL Server 

 DECLARE @s VARCHAR(max)= '/uk-en/contact-us/frequently-asked-questions/your-trip/there-wi-fi-access-in-the-eurostar-terminals-and-board-your-trains'

 SELECT REVERSE(SUBSTRING(REVERSE(@s),CHARINDEX('/',REVERSE(@s)),LEN(REVERSE(@s))))+REVERSE(REPLACE(SUBSTRING(REVERSE(@s),1,CHARINDEX('/',REVERSE(@s))-1),'-',' '))

Sorry this was for SQL Server

did you try using split in big query

SPLIT('str' [, 'delimiter']) Returns a set of substrings as a repeated string. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.

Upvotes: 0

Related Questions