Re: Replacing Ordinal Suffixes

Hi All,

>From: James Cloos

>The E'' syntax eats your backslashes.  For that version, try just:

>SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),'(\d)(st|nd|rd|th)',
>'\1', 'g');

Actually, I found that the double backslashes are required whether the E is
used or not:

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)

development=# select regexp_replace('300 North 126th Street',
'(\\d+)(?:st|nd|rd|th)', '\\1', 'gi');
WARNING:  nonstandard use of \\ in a string literal
LINE 1: select regexp_replace('300 North 126th Street', '(\\d+)(?:st...
                                                        ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
WARNING:  nonstandard use of \\ in a string literal
LINE 1: ...300 North 126th Street', '(\\d+)(?:st|nd|rd|th)', '\\1', 'gi...
                                                             ^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
    regexp_replace
----------------------
 300 North 126 Street
(1 row)

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

I appreciate the help of everyone who responded!

Cheers,
George