Yuck. This works and I’m recording it here in case I need it again but yuck.

Here’s the scenario: there is a column in a database with a delimited string. The number of delimited values can vary. SOmetimes there will be one delimited value, sometimes there will be twenty. For this specific case I needed to get the last two values. If we weren’t on GreenPlum I would probably go with using the REVERSE function combines with POSITION and SUBSTRING. But alas, REVERSE is not available in GreenPlum (Postgres 8.2). So after much head scratching I came up with the following which will get the last two values and then concatenate them together with a pipe delimiter (don’t ask, that’s what we needed to display in my use case).

create table phil_test (columnA character varying)
;
insert into phil_test(columnA) values ('a');
insert into phil_test(columnA)  values ('a,b,c,d');
insert into phil_test(columnA)  values ('a,b,c,d,e');
insert into phil_test(columnA)  values ('a,b,c,d,e,f');
;
select columnA
from phil_test
order by columnA
;
select 
columnA
, coalesce((string_to_array(columnA, ','))[(array_upper((string_to_array(columnA, ',')),1)-1)]||' | ','')
	||(string_to_array(columnA, ','))[array_upper((string_to_array(columnA, ',')),1)] as last_two
from phil_test
order by columnA

While working this out I found the following posts pretty dang helpful:

Stack Overflow: Split comma separated column data into additional columns

PostgreSQL 8.2: Arrays

PostgreSQL 8.2: Array Functions and Operators

PostgreSQL 8.2: String Functions and Operators (mostly let me know of the very few string functions available to me)

If you’ve got a better way to solve this problem let me know! Also give me a heads up if you’ve had a similar requirement.