Extract last 4 characters from string?

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: Postgres-General <pgsql-general(at)postgresql(dot)org>
Subject: Extract last 4 characters from string?
Date: 2007-12-12 21:11:50
Message-ID: 47604E96.6060505@lorenso.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

All,

Getting the first 4 characters from the begining of a string is easy enough:

SELECT SUBSTR('ABCDEFGHIJKLMNOP', 1, 4);

Returns 'ABCD'. But getting the last 4 characters appears to be a
little more work and is ugly:

SELECT SUBSTR('ABCDEFGHIJKLMNOP', LENGTH('ABCDEFGHIJKLMNOP') - 3, 4);

Returns 'MNOP'. I hate having to provide my input string more than once
like this. So ... uglier:

REGEXP_REPLACE('ABCDEFGHIJKLMNOP', '^.*(....)$', '\\1');

Returns 'MNOP'. Many languages have a version of substr that takes
negative arguments to begin offset from the end of the string like this:

SELECT SUBSTR('ABCDEFGHIJKLMNOP', -4);

That doesn't seem to work in PostgreSQL. In fact, it doesn't even error
out ... it just returns the whole string. Is there an easy (preferred)
method that I'm missing?

-- Dante

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ertel, Steve 2007-12-12 21:29:56 simple way to find the constraints
Previous Message Stephen Cook 2007-12-12 21:00:20 Re: top posting