find last value from comma separated column

Question

I have a comma separated text in a given column. Is there any way to get the last, second last and third last value?

select mnumber from mytable limit 2;

x, y, z, 1, 2, 3
a, b, c, d, e, f, g, h, 7, 8, 9

The values returned from this column should look something like this...

col3, col2 col1
1 2 3
7 8 9

This does not work though:

split_part(mnumber,',',-1) as last_col

It would have returned what I need if minus values were allowed in split_part function.

Answer 1

select reverse(split_part(reverse(message), ',', 3)) as third_last , 
reverse(split_part(reverse(message), ',', 2)) as second_last ,
reverse(split_part(reverse(message), ',', 1)) as my_last ,
message from my_table

Answer 2

this will give you last value for the column

select SPLIT_PART(mnumber, ',', (len(mnumber)/2+1)) from mytable

this will give third last value for the column

select SPLIT_PART(mnumber, ',', ((len(mnumber)/2)-1)) from mytable

This will give second last value for the column

select SPLIT_PART(mnumber, ',', (len(mnumber)/2)) from mytable