Re: Replacing Ordinal Suffixes

This is a kick *ss forum. I must say.


On Fri, 2014-02-28 at 14:17 -0800, Steve Atkins wrote:
> 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
comparisonpurposes.  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');
>
> Cheers,
>   Steve
>
>
>