Thread: Substring & escape Character

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.

what am i doing wrong?

,

Mike Weaver
Software Developer

5, 42 Ladner Street
O'Connor, WA, 6163
All correspondence:
PO Box Y3502
East St Georges Terrace
Perth WA 6832

P: (+618) 9331 2700
F: (+618) 9331 3733
M: 0403 385 181
W: http://www.corpusglobe.com/
E: mweaver@corpusglobe.com

This email is intended only for the use of the individual or entity named above and may contain information that is confidential. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this email is strictly prohibited. When addressed to our clients, any opinions or advice contained in this email are subject to the terms and conditions expressed in the governing Corpus Globe client engagement letter. If you have received this Email in error, please notify us immediately by return email or telephone +61 8 9331 2700 and destroy the original message. Thank You.

Michael Weaver <mweaver@corpusglobe.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

Well Yes and No, (was typo)
It is actually an INT8, but I change that to an INT and *poof* it works..  :P

I figured an INT8 was close enough to an INT, but it looks like the old adage; "Close enough isn't good enough" is applicable here.

Thanks for your help.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, 24 February 2003 11:09 AM
> To: Michael Weaver
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Substring & escape Character
>
>
> Michael Weaver <mweaver@corpusglobe.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
>