Re: 8.0.3 regexp_replace()...

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?

In SQL2003 standard terms this is a "null-call" function:

4.27 SQL-invoked routines
...
A null-call function is an SQL-invoked function that is defined to
return the null value if any of its input arguments is the null value.
A null-call function is an SQL-invoked function whose <null-call
clause> specifies RETURNS NULL ON NULL INPUT.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.