Thread: search/replace in update

search/replace in update

From
Gary Stainburn
Date:
Hi all,

here in England OFTEL, the governing body for all things telephone 
recent hand another phone number dialing code change (one of many).

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?
-- 
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000    


Re: search/replace in update

From
"Josh Berkus"
Date:
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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


RE: search/replace in update

From
Jeff Eckermann
Date:
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


Re: RE: search/replace in update

From
"Josh Berkus"
Date:
Jeff,

> 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.

Hm?  Darn!  I'd swear there was a "replace" function in the various
string manipulation functions.  My mistake.

> 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';

Hey, thanks!  Do I have to "CREATE TRUSTED LANGUAGE" to enable plperl?

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


Re: RE: search/replace in update

From
Alex Pilosov
Date:
On Thu, 14 Jun 2001, Josh Berkus wrote:

> Hey, thanks!  Do I have to "CREATE TRUSTED LANGUAGE" to enable plperl?
No, plperl doesn't need 'trusted' bit, and is sandboxed. 

-alex




Re: search/replace in update

From
Gary Stainburn
Date:
Hi Jeff,

Thanks for the response.  Although I've never used functions before, I kinda 
follow what you're doing.  Presumably the parameters are field, old string, 
new string.

However, I have two problems.

1) I don't know how I would call that function
2) when I tried to create the function it failed saying unrecognised 
landuage. The message I got was: 

ERROR:  Unrecognized language specified in a CREATE FUNCTION: 'plperl'.  
Recognized languages are sql, C, internal and the created procedural 
languages.

I'm running postgresql 7.0.2-2 installed from rpms onto RH6.1.

Gary

On Thursday 14 June 2001  4:42 pm, Jeff Eckermann wrote:
> 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


RE: search/replace in update

From
Jeff Eckermann
Date:
Gary,
To answer your questions in the order given:
*    Parameters are as you guess.  You could juggle them any way you
like, but I think easier to remember if they correspond to the language
being used.
*    You call the function by including it in your SQL, like:
UPDATE table
SET mobile_number = search_replace (mobile_number, '07889', '0589')
WHERE mobile_number LIKE '0589%';
Or, if you just want to check that it works, you could do:
*    SELECT mobile_number, search_replace (mobile_number, '07889',
'0589') AS new_number
FROM table
WHERE mobile_number LIKE '0589%';
*    Procedural languages are not enabled by default: you need to do
that.  Easiest way is to run "createlang" from the OS prompt (see "man
createlang" for details, or PostgreSQL documentation).  For plperl, there
are extra steps required, which you can find in the documentation under
"Procedural Languages".  The result of those extra steps will be the
creation of a file "plperl.so", which you should then copy to your
/usr/local/lib directory.  You can then run "createlang plperl <database
name>", and you will be good to go.
PostgreSQL has lots of functions built in, but if you need something that is
missing, you can probably find it in one of the supported procedural
languages, and "import" it by wrapping in a custom function in the manner
shown below.
HTH
Jeff

> -----Original Message-----
> From:    Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
> Sent:    Friday, June 15, 2001 6:31 AM
> To:    Jeff Eckermann; 'Josh Berkus'
> Cc:    pgsql-sql@postgresql.org
> Subject:    Re: search/replace in update
> 
> Hi Jeff,
> 
> Thanks for the response.  Although I've never used functions before, I
> kinda 
> follow what you're doing.  Presumably the parameters are field, old
> string, 
> new string.
> 
> However, I have two problems.
> 
> 1) I don't know how I would call that function
> 2) when I tried to create the function it failed saying unrecognised 
> landuage. The message I got was: 
> 
> ERROR:  Unrecognized language specified in a CREATE FUNCTION: 'plperl'.  
> Recognized languages are sql, C, internal and the created procedural 
> languages.
> 
> I'm running postgresql 7.0.2-2 installed from rpms onto RH6.1.
> 
> Gary
> 
> On Thursday 14 June 2001  4:42 pm, Jeff Eckermann wrote:
> > 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html