Skip site navigation (1) Skip section navigation (2)

Re: 8.0.3 regexp_replace()...

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 (view raw or flat)
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.

In response to

pgsql-general by date

Next:From: Michael FuhrDate: 2006-01-31 18:20:30
Subject: Re: libpq questions
Previous:From: nboutelierDate: 2006-01-31 17:34:21
Subject: Re: Can't get the field = ANY(array) clause to work...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group