Postgres Pro
Downloads
Home   >   mailing lists

Replacing Ordinal Suffixes - Mailing list pgsql-general

From George Weaver
Subject Replacing Ordinal Suffixes
Date
Msg-id D29C256161D941CA90C61D39135765E9@D420
Whole thread Raw
Responses Re: Replacing Ordinal Suffixes  (Paul Jungwirth <pj@illuminatedcomputing.com>)
Re: Replacing Ordinal Suffixes  (Steve Atkins <steve@blighty.com>)
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Matthew Chambers
Date:
Subject: high throughput 9.3, master/slave question
Next
From: Paul Jungwirth
Date:
Subject: Re: Replacing Ordinal Suffixes