On Tue, 31 Jan 2006, Martijn van Oosterhout wrote:
> On Mon, Jan 30, 2006 at 11:27:23AM -0800, rlee0001 wrote:
> > The problem was that SUBSTRING returns NULL if it cannot find any
> > matches for the pattern and when the second parameter to REPLACE
> > returns NULL, REPLACE returns NULL (which is idiotic). Using COALESCE I
> > ensure that is SUBSTRING cannot find a match that '' (empty string) is
> > sent to REPLACE. REPLACE then behaves as expected and replaces nothing.
>
> Well, the rule for STRICT functions (which replace is) is that if any
> of the arguments are NULL, the result is NULL. Most of the time this is
> what you want. IMHO the problem above is substring returning null. NULL
> should generally mean "unknown" and a substr that doesn't match
> certainly isn't unknown. Question is, what should it return then?
Sadly, that seems to me to match the SQL2003 semantics for its regular
expression substring search. Or at least I believe that's what 6.29 GR5g
is implying.