Re: REGEXP_REPLACE woes

On Tue, Jun 10, 2008 at 01:28:06PM +0200, Leif B. Kristensen wrote:
> I want to transform the text '[p=1242|John Smith]' to
> <a href="./family.php?person=1242">John Smith</a>, but what I get is:
>
> pgslekt=> select REGEXP_REPLACE('[p=1242|John Smith]',
> pgslekt(> E'[p=(\d+)|(.+?)]',
> pgslekt(> E'<a href="./family.php?person=\\1">\\2</a>');
>                     regexp_replace
> ------------------------------------------------------
>  [<a href="./family.php?person="></a>=1242|John Smith]
> (1 row)
>
> What am I doing wrong?

Parts of the regular expression need more escaping.  Try this:

select regexp_replace(
   '[p=1242|John Smith]',
  e'\\[p=(\\d+)\\|(.+?)\\]',
  e'<a href="./family.php?person=\\1">\\2</a>'
);

                  regexp_replace
---------------------------------------------------
 <a href="./family.php?person=1242">John Smith</a>

Caution: this method doesn't do HTML entity escaping so if your
input isn't trustworthy then you could end up with HTML that's
different from what you intended.

--
Michael Fuhr