On 13rd of November 2020, Tom Lane committed patch:
Support negative indexes in split_part(). This provides a handy way to get, say, the last field of the string. Use of a negative index in this way has precedent in the nearby left() and right() functions. The implementation scans the string twice when N < -1, but it seems likely that N = -1 will be the huge majority of actual use cases, so I'm not really excited about adding complexity to avoid that. Nikhil Benesch, reviewed by Jacob Champion; cosmetic tweakage by me Discussion: https://postgr.es/m/cbb7f861-6162-3a51-9823-97bc3aa0b638@gmail.com
This is pretty cool.
Let's assume you have a dot separated string, like a hostname blob.home.depesz.com.
If you'd want to get last part, previously, you'd have to resort to using regexps or some other trickery.
Now, though, you can easily:
$ SELECT split_part('blob.home.depesz.com', '.', -1); split_part ──────────── com (1 ROW)
Same way you can get 2nd to last:
$ select split_part(‘blob.home.depesz.com', ‘.', -2);
split_part
────────────
depesz
(1 row)
Very cool. Not huge change, but a welcome one definitely. Thanks to all involved.