Athena SPLIT_PART last element

Question

I have no normalised fields in a no-sql json extract queried from ATHENA.

I would like to get the last value of my field.

fields examples:

Raleigh, NC, USA
Frankfurt, Germany

In the idea i would like something like this to select last element:

SPLIT_PART(city, ',' , last_element ) AS country

I don't know if i use the right function to perform this.

Bonus: how to select a field with like value.from without raise sql error ? :)

user_1144035 · Answer

You can use regexp_extract():

select regexp_extract(city, '[^ ,]+$')

This returns the last set of strings in city that do not contain spaces or commas -- the last element you are looking for.