From chestercyoung@yahoo.com Fri Sep 29 21:31:17 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 00FC09FB210 for ; Fri, 29 Sep 2006 18:31:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 86772-08 for ; Fri, 29 Sep 2006 21:31:14 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web54315.mail.yahoo.com (web54315.mail.yahoo.com [206.190.49.125]) by postgresql.org (Postfix) with SMTP id 2B4349FA5F7 for ; Fri, 29 Sep 2006 18:31:14 -0300 (ADT) Received: (qmail 13314 invoked by uid 60001); 29 Sep 2006 21:31:12 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=uE5boQ6MYlmzPut/yA2UoScVJm/K/8tNdEGZdBzHza4ImJovmp6L88bUbYk//WP2uIoN8eRX/Ulj/OhNWhrCXZYmOj6I08mcusvAKex+rRYHPwyijlDNnsP1V2oXDlQ8l/EWA42ybT7Jk3nPa2FGYkRoan5u5Hl2tuV+r97t/2Q= ; Message-ID: <20060929213112.13312.qmail@web54315.mail.yahoo.com> Received: from [68.35.2.64] by web54315.mail.yahoo.com via HTTP; Fri, 29 Sep 2006 14:31:12 PDT Date: Fri, 29 Sep 2006 14:31:12 -0700 (PDT) From: chester c young Subject: regexp_replace usage To: sql pgsql MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-621425609-1159565472=:11394" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.356 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE X-Spam-Level: *** X-Archive-Number: 200609/175 X-Sequence-Number: 26386 --0-621425609-1159565472=:11394 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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? --------------------------------- All-new Yahoo! Mail - Fire up a more powerful email and get things done faster. --0-621425609-1159565472=:11394 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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?

All-new Yahoo! Mail - Fire up a more powerful email and get things done faster. --0-621425609-1159565472=:11394-- From mike@fuhr.org Mon Oct 2 03:56:21 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 052C69FB3CB for ; Mon, 2 Oct 2006 00:56:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 74109-03 for ; Mon, 2 Oct 2006 03:56:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by postgresql.org (Postfix) with ESMTP id 11E399FB3C6 for ; Mon, 2 Oct 2006 00:56:15 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.8/8.13.8) with ESMTP id k923uAMP089468 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Sun, 1 Oct 2006 21:56:13 -0600 (MDT) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.8/8.13.8) with ESMTP id k923uApQ042671; Sun, 1 Oct 2006 21:56:10 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.8/8.13.8/Submit) id k923uAUK042670; Sun, 1 Oct 2006 21:56:10 -0600 (MDT) (envelope-from mfuhr) Date: Sun, 1 Oct 2006 21:56:09 -0600 From: Michael Fuhr To: chester c young Cc: sql pgsql Subject: Re: regexp_replace usage Message-ID: <20061002035609.GA42444@winnie.fuhr.org> References: <20060929213112.13312.qmail@web54315.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20060929213112.13312.qmail@web54315.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200610/1 X-Sequence-Number: 26390 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 From chestercyoung@yahoo.com Mon Oct 2 15:15:20 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 9A1C09FB2D8 for ; Mon, 2 Oct 2006 12:15:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 81392-03 for ; Mon, 2 Oct 2006 15:15:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web54309.mail.yahoo.com (web54309.mail.yahoo.com [206.190.49.119]) by postgresql.org (Postfix) with SMTP id 8A1B29FB326 for ; Mon, 2 Oct 2006 12:15:07 -0300 (ADT) Received: (qmail 88143 invoked by uid 60001); 2 Oct 2006 15:15:04 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=FfXo+WTvOyWMLcxxB78lZE73W7+KRwQTmTOwZsvizCcEzDZPxoCLQXY/OJ40ibAdK7zjScxh9SlzhEoufYUF5cxUW0olUNX1IkE35RJsiJZmTSN071hu9xWt64T+hhDJyde5TUYoqMvZINFX3DTx2KYyAZ3v+gASOEjuUocVhO8= ; Message-ID: <20061002151503.88141.qmail@web54309.mail.yahoo.com> Received: from [216.184.28.50] by web54309.mail.yahoo.com via HTTP; Mon, 02 Oct 2006 08:15:03 PDT Date: Mon, 2 Oct 2006 08:15:03 -0700 (PDT) From: chester c young Subject: Re: regexp_replace usage To: Michael Fuhr Cc: sql pgsql In-Reply-To: <20061002035609.GA42444@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1159626860-1159802103=:80275" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.852 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_40_50, HTML_MESSAGE X-Spam-Level: *** X-Archive-Number: 200610/2 X-Sequence-Number: 26391 --0-1159626860-1159802103=:80275 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Thanks ! Michael Fuhr wrote: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) --------------------------------- How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates. --0-1159626860-1159802103=:80275 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Thanks !

Michael Fuhr <mike@fuhr.org> wrote:
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)

How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates. --0-1159626860-1159802103=:80275--