Re: Replacing Ordinal Suffixes

On Mar 1, 2014, at 11:45 AM, George Weaver <gweaver(at)shaw(dot)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 Apple Inc. 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 compatibility to support an app you’re using that’s not been updated, possibly accidentally.

Cheers,
Steve