Обсуждение: substr

i noticed that substr behaves a bit different in pgsql than perl

ie select foo from table where substr(foo,1,1) = 'X';

initially i thought it should be substr(foo,0,1) 

just wondering on the reasoning for this offset ?

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc        | Hub.Org Networking Services
jeff@pgsql.com        | jeff@hub.org
www.pgsql.com        | www.hub.org
1-902-542-0713        | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick  : bignose

Jeff MacDonald <jeff@hub.org> writes:
> i noticed that substr behaves a bit different in pgsql than perl
> ie select foo from table where substr(foo,1,1) = 'X';

> just wondering on the reasoning for this offset ?

Larry Wall and the SQL92 authors didn't talk to each other...

We are implementing SQL around here, not Perl, so we have to follow
the SQL spec's definition of substr().
        regards, tom lane

> Jeff MacDonald <jeff@hub.org> writes:
> > i noticed that substr behaves a bit different in pgsql than perl
> > ie select foo from table where substr(foo,1,1) = 'X';
> 
> > just wondering on the reasoning for this offset ?
> 
> Larry Wall and the SQL92 authors didn't talk to each other...

bastards :) now we gotta decide who to kill, larry or the sql folk.
i'd say the sql folk, 0 is just more inututive for computer counting.

but that's just me.. :) anyway thanks.

> 
> We are implementing SQL around here, not Perl, so we have to follow
> the SQL spec's definition of substr().
> 
>             regards, tom lane
> 

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc        | Hub.Org Networking Services
jeff@pgsql.com        | jeff@hub.org
www.pgsql.com        | www.hub.org
1-902-542-0713        | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick  : bignose