Re: Replacing Ordinal Suffixes

On Feb 28, 2014, at 2:04 PM, George Weaver <gweaver(at)shaw(dot)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');

Cheers,
Steve