From: Paul Jungwirth
Try this:
SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(\d)(st|nd|rd|th)', '\1', 'g');
Hi Paul,
No luck...
SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), E'(\d)(st|nd|rd|th)',
E'\1', 'g');
regexp_replace
------------------------
300 north 126th street
(1 row)
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@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?
Thanks,
George
--
_________________________________
Pulchritudo splendor veritatis.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
