Re: Substrings by Regular Expression

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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