Re: 8.0.3 regexp_replace()...

On Mon, 30 Jan 2006, rlee0001 wrote:

> I did get the code working. The function DDL follows:
>
> CREATE OR REPLACE FUNCTION "webadmin"."regexp_replacex" (source
> varchar, pattern varchar, replacement varchar) RETURNS varchar AS
> $body$
> DECLARE
> retvalue VARCHAR;
> BEGIN
> retvalue = "source";
> LOOP
> retvalue = REPLACE(retvalue, COALESCE(SUBSTRING(retvalue FROM
> "pattern"), ''), "replacement");
> EXIT WHEN retvalue = REPLACE(retvalue, COALESCE(SUBSTRING(retvalue
> FROM "pattern"), ''), "replacement");
> END LOOP;
> RETURN retvalue;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> 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).

Why do you say that? I'd say that's precisely the most reasonable answer.
You're asking to replace an unknown portion of a string (since NULL is
unknown, it might match some portion of the string, it might not) with
something else. The answer to that seems pretty unknown to me.