Re: REGEXP_REPLACE woes

On Tue, Jun 10, 2008 at 02:25:44PM +0200, Leif B. Kristensen wrote:
> 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]',

Something between my message and your shell appears to have converted
a few spaces to no-break spaces. A hex dump of your query shows the
following:

00000000 73 65 6c 65 63 74 20 72 65 67 65 78 70 5f 72 65 |select regexp_re|
00000010 70 6c 61 63 65 28 0a c2 a0 20 c2 a0 27 5b 70 3d |place(.   '[p=|

Notice the byte sequences "c2 a0", which is the UTF-8 encoding of
<U+00A0 NO-BREAK SPACE>. Apparently psql doesn't like that. I don't
see that sequence in my original message:

00000000 73 65 6c 65 63 74 20 72 65 67 65 78 70 5f 72 65 |select regexp_re|
00000010 70 6c 61 63 65 28 0a 20 20 20 27 5b 70 3d 31 32 |place(. '[p=12|

--
Michael Fuhr