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

user_8229534 0
Viewed 1273

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.

hiveql hive sql
2 Answers

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)
user_27003440

using split to get the last occurrence

select split(A.my_column,'news\/')[size(split(A.my_column,'news\/'))-1] lt
    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
    union all
    select "edition_xx/news/radio_today/news_xx" as my_column
    )

output

radio_today_news_xx
television_1.3
radio_today/news_xx
user_73439220
Related