Thread: Substrings by Regular Expression

Hello-

The 7.2 version distributed with RedHat 8.0 seems not
to work for substrings and POSIX regular expressions.

I think this should work....

pds=>  select substring( str1 from ',' ) from address;
ERROR:  pg_atoi: error in ",": can't parse ","

Am I missing something?

TIA
J. Drummey
drumeng@yahoo.com

On Fri, 14 Nov 2003, Jim Drummey wrote:

> Hello-
>
> The 7.2 version distributed with RedHat 8.0 seems not
> to work for substrings and POSIX regular expressions.
>
> I think this should work....
>
> pds=>  select substring( str1 from ',' ) from address;
> ERROR:  pg_atoi: error in ",": can't parse ","
>
> Am I missing something?

That functionality was added in 7.3 I believe.  You may wish
to upgrade.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Fri, 14 Nov 2003, Jim Drummey wrote:
>> pds=>  select substring( str1 from ',' ) from address;
>> ERROR:  pg_atoi: error in ",": can't parse ","
>> 
>> Am I missing something?

> That functionality was added in 7.3 I believe.  You may wish
> to upgrade.

Also, it's worth noting that AFAICT the above is illegal per SQL99
--- the textual SUBSTRING variants the spec defines are
        <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>
 

so it looks to me like you can't omit "FOR <escape>" if you want to
adhere to the letter of the spec.  We do allow omitting that clause
as a pretty-obvious extension ... but it's important to realize that
it *is* an extension.
        regards, tom lane