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