Postgres Pro
Downloads
Home   >   mailing lists

Re: regexp_replace grief - Mailing list pgsql-admin

From Armin Resch
Subject Re: regexp_replace grief
Date
Msg-id CAGo0LBQauGMyD89HLHa=CM7yB9eEKMnx5hgq1QY0wZ7RfroixQ@mail.gmail.com
Whole thread Raw
In response to Re: regexp_replace grief  (Albe Laurenz <laurenz.albe@wien.gv.at>)
Responses Re: regexp_replace grief  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-admin
Tree view
Thx, Albe. I tested both proposals, and I gravitate now more towards the E'' nomenclature since it avoids something like this:

$pg_bs_char = ( $dbh->{pg_server_version} >= 90100 ) ? "\\" : "\\\\";

Hoping for a long half life of the E'' nomenclature ...

-ar 


On Fri, Apr 12, 2013 at 5:16 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Armin Resch wrote:
> Not sure this is the right list to vent about this but here you go:
>
> I) select regexp_replace('BEFORE.AFTER','(.*)\..*','\1','g') "Substring"
> II) select regexp_replace('BEFORE.AFTER','(.*)\\..*','\\1','g') "Substring"
>
> Executing (II) against pg 8.4.4 or 9.0.4 yields 'BEFORE', but in order for 9.1.7 to yield the same one
> has to execute (I) .. bummer

To be immune against different settings of standard_conforming_strings,
use the extended string literal syntax:

select regexp_replace('BEFORE.AFTER',E'(.*)\\..*',E'\\1','g') "Substring";

That will work in all versions.

Yours,
Laurenz Albe

pgsql-admin by date:

Previous
From: Mike Broers
Date:
Subject: Re: after 9.2.4 patch vacuumdb -avz not analyzing all tables
Next
From: Alvaro Herrera
Date:
Subject: Re: regexp_replace grief