Обсуждение: Extract last 4 characters from string?

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

On Dec 12, 2007 4:11 PM, D. Dante Lorenso <dante@lorenso.com> wrote:
> Is there an easy (preferred) method that I'm missing?

select substring('ABCDEFGHIJKLMNOP' from '....$');

On Wed, 12 Dec 2007 15:11:50 -0600
"D. Dante Lorenso" <dante@lorenso.com> wrote:

>    SELECT SUBSTR('ABCDEFGHIJKLMNOP', -4);

there is an example in the manual... still "regexpyous"

http://www.postgresql.org/docs/8.1/static/functions-string.html

substring('Thomas' from '...$') -> mas

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Rodrigo De León wrote:
> On Dec 12, 2007 4:11 PM, D. Dante Lorenso <dante@lorenso.com> wrote:
>> Is there an easy (preferred) method that I'm missing?
> select substring('ABCDEFGHIJKLMNOP' from '....$');

Thanks!  Now, since I'd like to see a number in there, I'll do this:

   SELECT SUBSTRING('ABCDEFGHIJKLMNOP' FROM '.{4}$');

That does look a lot better than what I was cooking up ... appreciate
the help!

-- Dante