Great work there from Mike -- thanks. That's enough to convince me
that length is definitely preferable to end-position. I maintain my
preference for zero-based indexing, but I don't feel strongly about
it. As I said before (but the immediately contradicted by including
poorly edited old text :-) I'm quite happy to simplify by saying the
negative numbers are Just Pain Not Allowed.
_/|_ ___________________________________________________________________
/o ) \/ Mike Taylor <[log in to unmask]> http://www.miketaylor.org.uk
)_v__/\ "Writing is like driving a car at night. You can only see as
far as the headlights, but you make the whole trip that way" --
E. L. Doctorow.
Mike Rylander writes:
> On 8/17/06, Mike Taylor <[log in to unmask]> wrote:
> > Ray Denenberg writes:
> > > I'm trying to track down why we changed it from length to position.
> > > The discussion was among the Ed. Board, and as I recall there was a
> > > good reason but I haven't tracked it down yet (in the Ed. Board
> > > archive). If I can't find it we'll go with position. (Thoug that
> > > does destabalize the proposal.) The zero-base issue is more
> > > difficult, as it seems there is fairly overwhelming support for
> > > 1-based as far as I can see.
> >
> > I only agreed to 1-based because someone on list list asserted that
> > it's what most substring operations use. But if Mike Rylander would
> > like to repeat his helpful survey of other environments' substring
> > operations, I'd support whichever index-base is most widely used
> > elsewhere.
>
> Now look what I've gotten myself into ... ;)
>
> I'll go through the environments I use most, in no particular order whatsoever.
>
> There is some amount of discordance between different SQL
> implementations regarding the index base of the start position.
>
> Postgresql uses 1-based indexes in order to provide consistency with
> ordinal tuple position[1]. It does not allow negative length, but
> using negative (or zero) start position will place the start logically
> before the beginning of the string, thusly:
>
> poop=# select substr('123456',-2,5);
> substr
> --------
> 12
> (1 row)
>
> While that does have a brand of internal consistency, I wouldn't
> promote it here.
>
> Oracle[2], while being mostly 1-based also, allows a start position of
> zero which means exactly the same thing as 1. I think this is worse
> than Pg's plan, since there's less consistency.
>
> Just to add more inconsistency, MySQL[3] has decided to allow negative
> positions and uses that to mean "from the end of the string", however,
> the second parameter is still always length. They use 1-based start
> positions, and don't say anything about zero (and I don't use MySQL),
> so I can't comment on that piece.
>
> So that covers SQL implementations, but it doesn't really give much
> direction since there is so much disagreement over details. Arguably,
> Pg is the closest to the SQL spec, but the spec does not constrain
> extensions in most cases, so it's hard to point to anything solid,
> other than nominally 1-based with length.
>
> On to programming languages!
>
> We've seen Perl, PHP, C/C++ (0-based, length) and Python (no substr,
> just slice) ... another currently popular language, Ruby[4], uses
> 0-based start position and length for substr, and /also/ uses 0-based
> start position for slices when the '[]' method is handed a range (
> x..y ) instead of two numbers. Java[5], trying to be a rebel, uses
> the second param in its .substring() method for end-position (that's a
> /slice/, Sun!), but it is also 0-based. XPath[6] uses 1-based start
> position and length.
>
> So here are my ill considered, biased conclusions:
>
> Traditionally, string indexing is 0-based. This comes from looking at
> strings as arrays of characters, and for the purpose of extraction
> (with the goal of matching), this feels "right" to me. Those that
> buck real-programming convention and use 1-based position are doing so
> for local consistency (SQL -- 1-based ordinal position) or just
> because (Java -- any idea?) or because they are following one (or
> both) of the 1-based crowd (XPath -- following Java, and attempting to
> be a query language like SQL).
>
> Being a programmer, I'm more comfortable with 0-based string indexing,
> so that's what my heart would prefer. However, we are designing a
> query language here, so it makes sense to follow the convention of
> other query languages. Both SQL and XPath/XSLT have 1-based substring
> implementations, so I'm not going to put up too much of a fight to get
> 0-base substrings. I would, however, urge everyone to discard the
> idea of "logical" starting positions -- no zero or negatives, please!
> There's just too much confusion over what they should mean, and there
> are other methods that can be employed for providing "match the end of
> the string" stuff.
>
> As to lenghth vs end-position, every single implementation of substr
> uses length, with the exception of slice methods that happen to be
> spelled "substr". IMHO, if an end-position version of this method is
> desired/desirable, it should be called "slice". Note, however, that
> you can implement slice via substr by saying
>
> slice( string, substr-start, substr-start + substr-len - 1 )
>
> and substr via slice with
>
> substr( string, slice-start, slice-end - slice-start + 1 )
>
> So, if that's how you want to do it under the covers, or your language
> of choice doesn't have a proper substr implementation, you can
> transform to slice in the backend.
>
>
> 1) http://www.postgresql.org/docs/8.1/interactive/functions-string.html
> 2) http://www.adp-gmbh.ch/ora/sql/substr.html
> 3) http://mysql.com/doc/refman/5.0/en/string-functions.html
> 4) http://www.rubycentral.com/book/ref_c_string.html#String._ob_cb
> 5) http://mindprod.com/jgloss/substring.html
> 6) http://www.topxml.com/xsl/funcsubstring.asp
>
>
> >
> > (And for the same reason I prefer length to end.)
> >
> > _/|_ ___________________________________________________________________
> > /o ) \/ Mike Taylor <[log in to unmask]> http://www.miketaylor.org.uk
> > )_v__/\ "He looks around; he sees angels in the architecture, spinning
> > in infinity" -- Paul Simon, "Call Me Al"
> >
>
>
> --
> Mike Rylander
> [log in to unmask]
> GPLS -- PINES Development
> Database Developer
> http://open-ils.org
