Received: from nezu.gs.verio.net (nezu.gs.verio.net [204.27.65.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5EFgVE55157 for ; Thu, 14 Jun 2001 11:42:31 -0400 (EDT) (envelope-from jeckermann@verio.net) Received: by NEZU with Internet Mail Service (5.5.2650.21) id ; Thu, 14 Jun 2001 10:42:24 -0500 Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094AD7@NEZU> From: Jeff Eckermann To: "'Josh Berkus'" , Gary Stainburn Cc: pgsql-sql@postgresql.org Subject: RE: search/replace in update Date: Thu, 14 Jun 2001 10:42:23 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain X-Archive-Number: 200106/211 X-Sequence-Number: 3469 Which function would you be suggesting? I hope not "translate". That works on characters, not strings. Easy to confuse if you don't read closely enough, as I found to my cost. Easiest way using these functions would be: UPDATE table SET mobile_number = '07889' || substr (mobile_number, 5) WHERE mobile_number LIKE '0589%'; Alternatively, you could define a generic search/replace function like: CREATE FUNCTION search_replace (text, text, text) RETURNS text AS ' RETURN @_[0] =~ s/@_[1]/@_[2]/ ' LANGUAGE 'plperl'; Which would be available anytime for similar purposes (credit to Tom Lane for the idea). > -----Original Message----- > From: Josh Berkus [SMTP:josh@agliodbs.com] > Sent: Thursday, June 14, 2001 9:52 AM > To: Gary Stainburn > Cc: pgsql-sql@postgresql.org > Subject: Re: search/replace in update > > Gary, > > > This means that e.g. all mobile numbers that used to start 0589 now > > start 07889. > > > > Is there a way in SQL to update the phone number in-place? > > Postgresql has a nifty string substitution function. For this and other > character manipulation functions, see: > > http://www.postgresql.org/idocs/index.php?functions-string.html > > You should be able to fairly easily swap out "07889" and replace it with > "0589". > > -Josh > > > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster