substr negative indexes

From: Guy Rouillier <guyr-ml1(at)burntmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: substr negative indexes
Date: 2007-01-14 18:13:06
Message-ID: 45AA72B2.30502@burntmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Does the spec leave implementation of indexes on the substr() function
less than one undefined? By mistake, I had a substr() invocation with
an initial index of zero (would be nice if all the computer languages of
the world could agree to a single definition.) Oracle silently treats
this the same as 1 (one), while I just learned that PG treats indexes
less than 1 as a sliding window off the left of the string. Oracle
defines negative indexes to mean "from the end of the string."

So, given a string "abcdefg":

Oracle substr('abcdefg', 1, 4) = 'abcd'
Oracle substr('abcdefg', 0, 4) = 'abcd'
Oracle substr('abcdefg', -1, 4) = 'd'
PG substr('abcdefg', 1, 4) = 'abcd'
PG substr('abcdefg', 0, 4) = 'abc'
PG substr('abcdefg', -1, 4) = 'ab'

--
Guy Rouillier

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavan Deolasee 2007-01-14 18:51:15 Re: Autovacuum Improvements
Previous Message Michael Fuhr 2007-01-14 16:55:43 Re: Export to shape file