Re: Substring & escape Character

Michael Weaver <mweaver(at)corpusglobe(dot)com> writes:
> I am getting the error:
> ERROR: ESCAPE string must be empty or one character
> on the line:
> last := substring (name from 1 for pos);
> where name is a text field and pos is an integer.

I'll bet a nickel that pos is not, in fact, an integer. I get:

regression=# select substring ('this is a test'::text from 1 for 11::int);
substring
-------------
this is a t
(1 row)

regression=# select substring ('this is a test'::text from 1 for 11::text);
ERROR: ESCAPE string must be empty or one character

With a text third argument (or anything non-integral that can be cast to
text), the parser will probably decide that the closest match is the
SQL99 regexp substring function. The SQL committee didn't do anyone
any favors by inventing a bizarre special syntax for counted-substring
and then reusing it exactly for regexp-substring, but that's what they
did:

<character substring function> ::=
SUBSTRING <left paren> <character value expression>
FROM <start position>
[ FOR <string length> ] <right paren>

<regular expression substring function> ::=
SUBSTRING <left paren> <character value expression>
FROM <character value expression>
FOR <escape character> <right paren>

regards, tom lane