Replacing Ordinal Suffixes

["George Weaver" <gweaver@xxxxxxx>]

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?

 

Thanks,

George