Clever Geek Handbook

Find the last value from a comma separated column

I have comma separated text in a given column. Is there a 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:

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

It would give me back what I need if minus values ​​were allowed in the split_part function.

+3


source to share


2 answers 


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


+5


source


this will give you the last value for the column

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

this will give the third last value for the column



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

This will give the second last value for the column

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


source






More articles:


All Articles