|
From: Steve Atkins On Feb 28, 2014, at 2:04 PM, George Weaver <gweaver@xxxxxxx> 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'); Hi Steve, Thanks, but no luck: select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)', E'\1', 'gi'); regexp_replace ------------------------ 300 North 126th Street George Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general |
