Thread: regexp_replace usage

column name in table bue has miscapitalized Mc names, eg, 'John Mcneil' instead of 'John McNeil'.<br /><br />(this
shouldbe easy but) how do you construct the update query?<br /><br />also, regexp_string( 'Mcneil', 'Mc(.*)',
initcap('\\1')) => 'neil' _not_ Neil' - is this correct?<br /><p><hr size="1" /><a
href="http://us.rd.yahoo.com/evt=43256/*http://advision.webevents.yahoo.com/mailbeta">All-new Yahoo! Mail </a>- Fire up
amore powerful email and get things done faster. 

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

Thanks !<br /><br /><b><i>Michael Fuhr <mike@fuhr.org></i></b> wrote:<blockquote class="replbq"
style="border-left:2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;">Offhand I can't think of a way to
dowhat you want with regexp_replace()<br />but you could use PL/Perl. Something like this should work:<br /><br
/>CREATEFUNCTION mcfix(text) RETURNS text AS $$<br /> $_[0] =~ s/\bMc([a-z])/Mc\u$1/g;<br /> return $_[0];<br />$$
LANGUAGEplperl IMMUTABLE STRICT;<br /><br />SELECT mcfix('John Mcneil');<br /> mcfix <br />-------------<br /> John
McNeil<br/>(1 row)<br /><br /></blockquote><p><hr size="1" />How low will we go? Check out Yahoo! Messenger’s low <a
href="http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com">
PC-to-Phonecall rates.</a>