Yuck. This works and I'm recording it here in case I need it again but yuck.<\/p>\n
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).<\/p>\n
create table phil_test (columnA character varying)\r\n;\r\ninsert into phil_test(columnA) values ('a');\r\ninsert into phil_test(columnA) values ('a,b,c,d');\r\ninsert into phil_test(columnA) values ('a,b,c,d,e');\r\ninsert into phil_test(columnA) values ('a,b,c,d,e,f');\r\n;\r\nselect columnA\r\nfrom phil_test\r\norder by columnA\r\n;\r\nselect \r\ncolumnA\r\n, coalesce((string_to_array(columnA, ','))[(array_upper((string_to_array(columnA, ',')),1)-1)]||' | ','')\r\n\t||(string_to_array(columnA, ','))[array_upper((string_to_array(columnA, ',')),1)] as last_two\r\nfrom phil_test\r\norder by columnA\r\n<\/pre>\nWhile working this out I found the following posts pretty dang helpful:<\/p>\n
Stack Overflow: Split comma separated column data into additional columns<\/a><\/p>\nPostgreSQL 8.2: Arrays<\/a><\/p>\n