postgresql string_to_array - get the last and one before last elements

Question

I have a long string contained of int,int,float,float * many and I need to get the last two floats.

100,140,14.123,15.123,200,240,16.124,17.123

I'm using string_to_array to get element 3 & 4 (first two floats) but I also need a way to get the last two floats (places 7 & 8 in this example).

I did it a in two iterations, first added a column of the array length: > UPDATE table SET array_length = array_length(string_to_array(column_to_array,','), 1); than: > SELECT (string_to_array(column_to_array,','))[array_length -1], (string_to_array(column_to_array,','))[array_length] FROM table — OLS
– OLS, Commented May 11, 2014 at 19:56
The real question is: why are you storing the data like that? Never ever store comma separated values in a single column. — user330315
– user330315, Commented May 11, 2014 at 22:11

Pavel Stehule · Accepted Answer · 2014-05-11 20:00:39Z

2

try

CREATE OR REPLACE FUNCTION last_fields(anyarray, int)
RETURNS anyarray LANGUAGE sql AS $$
  SELECT ($1)[array_upper($1,1) - $2 + 1: array_upper($1,1)];
$$;

postgres=# select last_fields(ARRAY[1,2,3,4,5], 2);
 last_fields 
─────────────
 {4,5}
(1 row)

answered May 11, 2014 at 20:00

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql string_to_array - get the last and one before last elements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related