pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedregexp_replace usage
3+ messages / 2 participants
[nested] [flat]
* regexp_replace usage
@ 2006-09-29 21:31 chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: chester c young @ 2006-09-29 21:31 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: regexp_replace usage
@ 2006-10-02 03:56 Michael Fuhr <mike@fuhr.org>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Fuhr @ 2006-10-02 03:56 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: regexp_replace usage
@ 2006-10-02 15:15 chester c young <chestercyoung@yahoo.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 3+ messages in thread
From: chester c young @ 2006-10-02 15:15 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-10-02 15:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-29 21:31 regexp_replace usage chester c young <chestercyoung@yahoo.com>
2006-10-02 03:56 ` Michael Fuhr <mike@fuhr.org>
2006-10-02 15:15 ` chester c young <chestercyoung@yahoo.com>
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