PostgreSQL get last value in a comma separated list of values

In a PostgreSQL table I have a column which has values like

AX,B,C
A,BD
X,Y
J,K,L,M,N

In short , it will have a few comma separated strings in the column for each record. I wanted to get the last one in each record. I ended up with this.

select id, reverse(substr(reverse(mycolumn),1,position(',' in reverse(mycolumn)))) from mytable order by id ; 

Is there an easier way?

Answers


With regexp_replace:

select id, regexp_replace(mycolumn, '.*,', '')
from mytable
order by id;

I would do it this way:

select reverse(split_part(reverse(myColumn), ',', 1))

Is there an easier way?

With your current data, Gordon's answer works best imo. Other options would be a regex (messy), or converting the column to a text[] array e.g. ('{' || col || '}')::text[] or variations thereof.

If you were using a text[] array instead of plain text for your column, you'd want to use array functions directly:

select col[array_length(col, 1)]

http://www.postgresql.org/docs/current/static/functions-array.html

Example with dummy data:

 with bar as (
 select '{a,b,c}'::text[] as foo
 )
 select foo[array_length(foo, 1)] from bar;

You could, of course, also create a parse_csv() function or get_last_csv_value() function to avoid writing the above.


Need Your Help

No module named win32console, while running wexpect

python expect pexpect

I want to run wexpect (the windows port of pexpect) on my Windows 7 64-bit machine. I am getting the following error:

Downsample 16-bit pcm audio to 8-bit in android

android audio pcm

I would like to downsample 16 bit pcm audio to 8 bit and then upsample the same from 8 bit back to 16 bit in android. I am using this which seems to work: