----- Original Message -----
From: Steve Atkins
To: pgsql-general
Sent: Friday, February 28, 2014 4:17 PM
Subject: Re: Replacing Ordinal Suffixes
On Feb 28, 2014, at 2:04 PM, George Weaver <gweaver@xxxxxxx> wrote:
Hi list,
I'm stumped.
I am trying to use Regexp_Replace to replace ordinal suffixes in addresses
(eg have '126th' want '126') for comparison purposes. So far no luck.
I have found that
SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(?!/D)(st|nd|rd|th)', '', 'g');
regexp_replace
------------------
300 nor 126 reet
but
SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(?=/D)(st|nd|rd|th)', '', 'g');
regexp_replace
------------------------
300 north 126th street
I'm a novice with regular expressions and google hasn't helped much.
Any suggestions?
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
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
