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