CODEWITHSUNDEEP
sqlregexstringhiveqldelimiter
pp2000
pp2000

Reputation: 35

Hive Delimiter using :

I want to extract a column A that has values such as W:X:Y:Z. I am interested to extract Z from Column A. I tried multiple commands such as SPLIT(Table.A, "[:]"[3] ) but get an error. What is the best way to do this?

Upvotes: 1

Views: 47

Answers (1)

leftjoin
leftjoin

Reputation: 38290

Split function returns array. Array index [3] should be applied to the split function result:

with yourtable as ( -- use your table instead of this
select 'W:X:Y:Z' as A
)

select split(A,'\\:')[3] from yourtable;

Result:

Z

Upvotes: 1

Related Questions