Re: Substring & escape Character

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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Monday, 24 February 2003 11:09 AM
> To: Michael Weaver
> Cc: pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] 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
>