Re: REGEXP_REPLACE woes

On Tuesday 10. June 2008, Michael Fuhr wrote:
>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>

Thank you Michael, I figured it was something fishy with the escaping.
When I try your example, I get

pgslekt=> select regexp_replace(
pgslekt(>    '[p=1242|John Smith]',
pgslekt(>   e'\\[p=(\\d+)\\|(.+?)\\]',
pgslekt(>   e'<a href="./family.php?person=\\1">\\2</a>'
pgslekt(> );
ERROR: syntax error at or near " "
LINE 2:    '[p=1242|John Smith]',

But with my own doctored code, it works just fine:

pgslekt=> select REGEXP_REPLACE(E'[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>
(1 row)

>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.

The input is all my own from 127.0.0.1, so it's of course totally
trustworthy :-)
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/