[GENERAL] Replacing Ordinal Suffixes

[George Weaver]

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