Substring result short by 1

[David Filion]

Hi,

I have a question about substring(), when I run the following query:

prepaid=# select substring('1514 8300', 0, 5);
substring
-----------
1514
(1 row)
I get a result with only the first 4 characters, not five. Why is
that? The documentation doesn't spend much time discussing the
substring() function and google turned up nothing. I tried
searching the archives but the site is down.

David

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[gnari]

From: "David Filion" <da***@filionte ch.com>

prepaid=# select substring('1514 8300', 0, 5);

try substring('1514 8300', 1, 5);

gnari

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

David Filion <da***@filionte ch.com> writes:

prepaid=# select substring('1514 8300', 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly