Search code examples
hiveimpala

Extract the last element of a list for a split string

I'm trying to take a regular expression and split it by a pre-determined character, and then extract the final value of the returned list.

For example, my string may take the form:

name
WAYNE.ROONEY.226
ROSS.BARKLEY.HELLO.113
ADAM.A122

Pythonically, what I'm trying to do is:

for x in list:
  my_val = x.split('.')[-1] #Return the last element of the list when split on .

e.g. desired output:

name                         value
WAYNE.ROONEY.226             226
ROSS.BARKLEY.HELLO.113       113
ADAM.A122                    A122

Can anyone provide me any pointers in either Hive or Impala please?

If I can create this as a view, ideally, that would be perfect, but am also happy with generating actual output with it and then re-uploading to a table

Thank you!

Solution

  • For Hive:

    select regexp_extract(NAME, '\\.([^\\.]+)$', 1) as VALUE
from WHATEVER

    And pleeeease [edit] learn the power of regular expressions...