Обсуждение: 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