Re: Replacing Ordinal Suffixes

From: Paul Jungwirth <pj(at)illuminatedcomputing(dot)com>
To: George Weaver <gweaver(at)shaw(dot)ca>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Replacing Ordinal Suffixes
Date: 2014-02-28 22:16:24
Message-ID: CA+6hpa=D=GvA2uQSWcgBY0cDaswdRBminzcksGqbUnpqFsDNxQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Try this:

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(\d)(st|nd|rd|th)', '\1', 'g');

Note that matching a number is \d not /D: backslash, not forward
slash, and lowercase d not uppercase. \d means a digit, \D means
anything except a digit.

Also, I don't think Postgres supports positive lookbehind expressions
(which are actually (?<=foo), not (?!foo)), but you can get the same
effect by capturing the number with (\d) and then outputting it again
with the \1.

Paul

On Fri, 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?
>
> Thanks,
> George

--
_________________________________
Pulchritudo splendor veritatis.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Atkins 2014-02-28 22:17:20 Re: Replacing Ordinal Suffixes
Previous Message George Weaver 2014-02-28 22:04:22 Replacing Ordinal Suffixes