Re: Substring & escape Character - Mailing list pgsql-novice

From Michael Weaver
Subject Re: Substring & escape Character
Date
Msg-id 3B663B41B350D311AEEF00A0C9254563201A7C@VISION1
Whole thread Raw
In response to Substring & escape Character  (Michael Weaver <mweaver@corpusglobe.com>)
List pgsql-novice

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
>

pgsql-novice by date:

Previous
From: Andrew McMillan
Date:
Subject: Re: GPL Licence
Next
From: Wim
Date:
Subject: Wrapping text