impala split_part function not accepting negative indexing

Question

I'm trying to access the second to last element in a split_part function. as I saw you should be able to use a negative index to look right to left. In impala however is get a "invalid field position" error

SELECT split_part(string1;string2;string3;string4, ";", -2)

SELECT split_part(string3;string4, ";", -2)

SELECT split_part(string2;string3;string4, ";", -2)

I was hoping to get "string3" returned for all the above examples. Any help on how to do this in Impala would be much appreciated

Answer 1

that argument must be >=1. So you can use below workaround - you calculate position of last but one string and then use split part accordingly. You can use below code.

SELECT split_part(stc_column, ';', length(stc_column)-length(replace(stc_column,';','')))

Here is output for your input scenario -

SELECT split_part('string2;string3;string4', ';', length('string2;string3;string4') - length(replace('string2;string3;string4',';',''))  
)