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.