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.
