Remove last x characters until a specific character

Question

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!

It sounds like bigquery can use JavaScript functions in which case you could try [something like](http://stackoverflow.com/questions/958908/how-do-you-reverse-a-string-in-place-in-javascript) — Brad, Feb 12 '15 at 15:40

score 2 · Answer 1 · answered Feb 12 '15 at 15:46

2

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)

answered Feb 12 '15 at 15:46

Mosha Pasumansky

13,206
5
32
55

akhil vangala · Answer 2 · 2015-02-12T15:24:52.877

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.

BigQuery doesn't recognize the REVERSE function. – Doppia L Feb 12 '15 at 15:19 — Doppia L, Feb 12 '15 at 15:19

Remove last x characters until a specific character

2 Answers2