From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | rlee0001 <robeddielee(at)hotmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: 8.0.3 regexp_replace()... |
Date: | 2006-01-31 18:05:42 |
Message-ID: | 20060131100235.I54974@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-01-31 18:20:30 | Re: libpq questions |
Previous Message | nboutelier | 2006-01-31 17:34:21 | Re: Can't get the field = ANY(array) clause to work... |