How to extract string after last occurrence of a word in Hive

Question

I have my string column in one of my Hive tables as

select * from
(
select "edition_xx/news/radio_today_news_xx" as my_column
union all 
select "edition_xx/news/news/television_1.3" as my_column
) A 

I would like to extract the part of string that is followed after news/ . So my output column would look like

radio_today_news_xx
television_1.3

How can I extract this using regex in Hive? Note that news/ can occur X times and I want the string after its last occurrence.

Answer 1

Use split():

select  split(my_column,'(news/)+')[1] 
from
(
select "edition_xx/news/radio_today_news_xx" as my_column
union all 
select "edition_xx/news/news/television_1.3" as my_column
) A;

This regexp means news/ one or more times

Result:

radio_today_news_xx
television_1.3
Time taken: 37.218 seconds, Fetched: 2 row(s)