From: Michael Fuhr <mike@fuhr.org>
To: chester c young <chestercyoung@yahoo.com>
Cc: sql pgsql <pgsql-sql@postgresql.org>
Subject: Re: regexp_replace usage
Date: Sun, 1 Oct 2006 21:56:09 -0600
Message-ID: <20061002035609.GA42444@winnie.fuhr.org> (raw)
In-Reply-To: <20060929213112.13312.qmail@web54315.mail.yahoo.com>
References: <20060929213112.13312.qmail@web54315.mail.yahoo.com>
On Fri, Sep 29, 2006 at 02:31:12PM -0700, chester c young wrote:
> column name in table bue has miscapitalized Mc names, eg, 'John Mcneil'
> instead of 'John McNeil'.
>
> (this should be easy but) how do you construct the update query?
>
> also, regexp_string( 'Mcneil', 'Mc(.*)', initcap('\\1') ) => 'neil'
> _not_ Neil' - is this correct?
I assume you mean regexp_replace() as indicated in the subject.
You're calling initcap() on the literal value '\\1'; the result,
which is the same string, is then passed to regexp_replace(), so
in effect you're calling
regexp_replace('Mcneil', 'Mc(.*)', '\\1')
Offhand I can't think of a way to do what you want with regexp_replace()
but you could use PL/Perl. Something like this should work:
CREATE FUNCTION mcfix(text) RETURNS text AS $$
$_[0] =~ s/\bMc([a-z])/Mc\u$1/g;
return $_[0];
$$ LANGUAGE plperl IMMUTABLE STRICT;
SELECT mcfix('John Mcneil');
mcfix
-------------
John McNeil
(1 row)
You could use
$_[0] =~ s/\b(Ma?c)([a-z])/$1\u$2/g;
to change Mcdonald and Macdonald into McDonald and MacDonald,
respectively. However, since both Macdonald and MacDonald are used,
determining which is correct would be impossible.
--
Michael Fuhr
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, chestercyoung@yahoo.com
Subject: Re: regexp_replace usage
In-Reply-To: <20061002035609.GA42444@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox