ducky801
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:
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
--------------------------
| 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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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_colum n))[0][0]) FROM your_table
tested on my machine and it returned the last element.
select reverse(sentences(reverse( 'abc,def,g hi'))[0][0 ]) from default.dual
SELECT reverse(sentences(reverse(
tested on my machine and it returned the last element.
select reverse(sentences(reverse(
How about this?
Open in new window
OR
Open in new window