Re: Replacing Ordinal Suffixes

----- Original Message -----
From: Steve Atkins
To: pgsql-general
Sent: Friday, February 28, 2014 4:17 PM
Subject: Re: [GENERAL] Replacing Ordinal Suffixes



On Feb 28, 2014, at 2:04 PM, George Weaver <gweaver@shaw.ca> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general