Link to home
Start Free TrialLog in
Avatar of ducky801
ducky801Flag for United States of America

asked on

Hive / SQL select last item from values separated by commas

I've got a field that holds values separated by commas.  How can i get the *LAST* value from the list?

---------------------------
|    Example_Field   |
---------------------------
[apple, orange, pear]
[apple, pear]    
[pear, pear, apple]
[orange]
---------------------------

I want a select query to return the last value from each list:
pear
pear
apple
orange

So:
Select SpecialFunctionToGetLastVal(Example_Field) as LastFruit from Table

Open in new window


This is for a hive query, but i'll cross that bridge when i come to it.  Is there some generic function or method in SQL to do this?

Thanks in advance
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry this is wrong. - my first solution  that is.

How about this?

DECLARE @LastVAR NVARCHAR(100)
DECLARE MYTESTCURSOR CURSOR
DYNAMIC 
FOR
SELECT Example_Field as lastItem FROM yourtable
OPEN MYTESTCURSOR
FETCH LAST FROM MYTESTCURSOR INTO @LastVAR 
CLOSE MYTESTCURSOR
DEALLOCATE MYTESTCURSOR
SELECT @LastVAR 

Open in new window


OR

set rowcount 1
 
select * from yourtable order by id desc

Open in new window

I'm not very familiar with Hive, but I do know it has string functions for reverse(), instr(), trim(), replace(), and substr() from which you could create a user_defined function. In pseudo code, something like the following:
if instr(field,',') = 0 then field
else
reverse(field)
find first position of reversed field using instr(reverse(field),',')
substr(reverse(field),1, instr(reverse(field),',')
replace the ',' from above, then trim the result
and finally reverse that.
Sorry I couldn't be more specific but I hope this might help.
Is it a STRING or ARRAY data type? And do you have open and closing brackets also as part of the string?
try this.

SELECT reverse(sentences(reverse(your_column))[0][0]) FROM your_table

tested on my machine and it returned the last element.

select reverse(sentences(reverse('abc,def,ghi'))[0][0]) from default.dual