Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting the last word from a Postgres string, declaratively

[EDIT] original title of this question was "Getting the last element of a Postgres array, declaratively"

How to obtain the last element of the array in Postgres?

I need to do it declaratively as I want to use it as a ORDER BY criteria. I wouldn't want to create a special PGSQL function for it, the less changes to the database the better in this case.

In fact, what I want to do is to sort by the last word of a specific column containing multiple words. Changing the model is not an option here.

In other words, I want to push Ruby's sort_by {|x| x.split[-1]} into the database level. I can split a value into array of words with Postgres string_to_array or regexp_split_to_array functions, then how to get its last element?

like image 812
Wojciech Kaczmarek Avatar asked Jun 01 '10 12:06

Wojciech Kaczmarek


People also ask

How do I get the last character of a string in PostgreSQL?

The PostgreSQL RIGHT() function returns the last n characters in a string.

How do I split a string in PostgreSQL?

The PostgreSQL SPLIT_PART() function is used to split a string from a specific delimiter and these queries return the nth substring. Let's analyze the above syntax: The string argument is the string to be split. The delimiter is a string used as the delimiter for splitting.

What does $$ mean in PostgreSQL?

In PostgreSQL, the dollar-quoted string constants ($$) is used in user-defined functions and stored procedures. In PostgreSQL, you use single quotes for a string constant like this: select 'String constant'; When a string constant contains a single quote ('), you need to escape it by doubling up the single quote.

How do I get the first character of a string in PostgreSQL?

The PostgreSQL LEFT() function returns the first n characters in the string.


2 Answers

If I understand your question correctly you have a string and you're first splitting it on some separator and then afterwards finding the last element of the array and discarding the rest.

You could miss out the middle man and get the last element directly:

SELECT regexp_replace('foo bar baz', '^.* ', '') 

Result:

baz 
like image 128
Mark Byers Avatar answered Sep 23 '22 01:09

Mark Byers


Use array_upper():

SELECT array_upper(ARRAY[1,2,5,6], 1); 
like image 40
Frank Heikens Avatar answered Sep 20 '22 01:09

Frank Heikens