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
>
>
>