Skip site navigation (1) Skip section navigation (2)

Re: Substring result short by 1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Filion <david(at)filiontech(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Substring result short by 1
Date: 2004-08-31 20:49:18
Message-ID: 15048.1093985358@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
David Filion <david(at)filiontech(dot)com> writes:
> prepaid=# select substring('15148300', 0, 5);
> substring
> -----------
> 1514
> (1 row)

> I get a result with only the first 4 characters, not five.  Why is 
> that?

AFAICS this is per spec.  It's a bit odd that SQL doesn't consider
start position less than 1 as an error, but that's how the spec
is written:

            a) Let C be the value of the <character value expression>, let
              LC be the length of C, and let S be the value of the <start
              position>.

[ so for your example, LC = 8, S = 0 ]

            b) If <string length> is specified, then let L be the value of
              <string length> and let E be S+L. Otherwise, let E be the
              larger of LC + 1 and S.

[ L = 5, E = S+L = 5 ]

            c) If either C, S, or L is the null value, then the result of
              the <character substring function> is the null value.

[ nope ]

            d) If E is less than S, then an exception condition is raised:
              data exception-substring error.

[ nope ]

            e) Case:

              i) If S is greater than LC or if E is less than 1, then the
                 result of the <character substring function> is a zero-
                 length string.

[ nope ]

             ii) Otherwise,

                 1) Let S1 be the larger of S and 1. Let E1 be the smaller
                   of E and LC+1. Let L1 be E1-S1.

[ S1 = 1, E1 = 5, L1 = 4 ]

                 2) The result of the <character substring function> is
                   a character string containing the L1 characters of C
                   starting at character number S1 in the same order that
                   the characters appear in C.

[ result = what you got ]

The only case in which you can get an error is by specifying a negative
L.  Otherwise, you get whatever part of the string overlaps your
subscript range specification --- at either end.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Michal TaborskyDate: 2004-08-31 20:54:34
Subject: Re: Large Databases
Previous:From: Tom LaneDate: 2004-08-31 20:41:40
Subject: Re: zombie primary key lurches out of database to devour the brains of the unwary

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group