Re: Replacing Ordinal Suffixes

From: Ian Lawrence Barwick
>
>> ----- Original Message ----- From: Steve Atkins
>
>
>> On Feb 28, 2014, at 2:43 PM, George Weaver <gweaver(at)shaw(dot)ca> wrote:
>>
>> >Maybe this?
>>
>> >select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)',
>> >'\1', 'gi');
>>
>> Hi Steve,
>>
>> Thanks, but no luck:
>>
>> select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)',
>> E'\1', 'gi');
>> regexp_replace
>> ------------------------
>> 300 North 126th Street
>>
>> George
>
>
>> Those E’s you added completely change the meaning. If you want to
>> use E-style literals (and you probably don’t) you’ll need to double the
>> backslashes in all the strings.
>
>
> Hi Steve,
>
> Without the E's:
>
> development=# select regexp_replace('300 North 126th Street',
> '(\d+)(?:st|nd|rd|th)', '\1', 'gi');
> WARNING: nonstandard use of escape in a string literal
> LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
> ^
> HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
> WARNING: nonstandard use of escape in a string literal
> LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
> ^
> HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
>
> regexp_replace
> ------------------------
> 300 North 126th Street
> (1 row)
>
> Frustrating...

Per Steve Atkin's note about double backslashes:

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

Hi Ian,

I just got that as well - awesome!

Regards

Ian Barwick