Re: Replacing Ordinal Suffixes

On Mar 1, 2014, at 11:45 AM, George Weaver <gweaver@shaw.ca> wrote:

>
>> Actually, I found that the double backslashes are required whether the E is
>> used or not:
>
>> You must be using a relatively old PG version then.  Default behavior
>> since around 9.1 has been that backslashes aren't special except
>> in E'' strings.
>
> Hmm.
>
> development=# select version();
>                          version
> ------------------------------------------------------------
> PostgreSQL 9.1.9, compiled by Visual C++ build 1500, 32-bit
> (1 row)

steve=# select version();
                                                                                 version

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.4 on x86_64-apple-darwin12.0.0, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on
AppleInc. build 5658) (LLVM build 2336.11.00), 64-bit 
(1 row)

steve=# select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi');
    regexp_replace
----------------------
 300 North 126 Street
(1 row)


I suspect you have standard_conforming_strings set to off (it defaults to on in 9.1), possibly for backwards
compatibilityto support an app you’re using that’s not been updated, possibly accidentally. 

Cheers,
  Steve