pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
search/replace in update
7+ messages / 4 participants
[nested] [flat]

* search/replace in update
@ 2001-06-14 08:40 Gary Stainburn <gary.stainburn@ringways.co.uk>
  2001-06-14 14:52 ` Re: search/replace in update Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Gary Stainburn @ 2001-06-14 08:40 UTC (permalink / raw)
  To: pgsql-sql

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 
    



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: search/replace in update
  2001-06-14 08:40 search/replace in update Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2001-06-14 14:52 ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2001-06-14 14:52 UTC (permalink / raw)
  To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: search/replace in update
@ 2001-06-14 15:42 Jeff Eckermann <jeckermann@verio.net>
  2001-06-14 16:38 ` Re: RE: search/replace in update Josh Berkus <josh@agliodbs.com>
  2001-06-15 11:30 ` Re: search/replace in update Gary Stainburn <gary.stainburn@ringways.co.uk>
  0 siblings, 2 replies; 7+ messages in thread

From: Jeff Eckermann @ 2001-06-14 15:42 UTC (permalink / raw)
  To: 'Josh Berkus' <josh@agliodbs.com>; Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: RE: search/replace in update
  2001-06-14 15:42 RE: search/replace in update Jeff Eckermann <jeckermann@verio.net>
@ 2001-06-14 16:38 ` Josh Berkus <josh@agliodbs.com>
  2001-06-14 17:32   ` Re: RE: search/replace in update Alex Pilosov <alex@pilosoft.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Josh Berkus @ 2001-06-14 16:38 UTC (permalink / raw)
  To: Jeff Eckermann <jeckermann@verio.net>; 'Josh Berkus' <josh@agliodbs.com>; Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: RE: search/replace in update
  2001-06-14 15:42 RE: search/replace in update Jeff Eckermann <jeckermann@verio.net>
  2001-06-14 16:38 ` Re: RE: search/replace in update Josh Berkus <josh@agliodbs.com>
@ 2001-06-14 17:32   ` Alex Pilosov <alex@pilosoft.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Alex Pilosov @ 2001-06-14 17:32 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: search/replace in update
  2001-06-14 15:42 RE: search/replace in update Jeff Eckermann <jeckermann@verio.net>
@ 2001-06-15 11:30 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
  1 sibling, 0 replies; 7+ messages in thread

From: Gary Stainburn @ 2001-06-15 11:30 UTC (permalink / raw)
  To: Jeff Eckermann <jeckermann@verio.net>; 'Josh Berkus' <josh@agliodbs.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* RE: search/replace in update
@ 2001-06-15 14:25 Jeff Eckermann <jeckermann@verio.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Jeff Eckermann @ 2001-06-15 14:25 UTC (permalink / raw)
  To: 'Gary Stainburn' <gary.stainburn@ringways.co.uk>; 'Josh Berkus' <josh@agliodbs.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2001-06-15 14:25 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-14 08:40 search/replace in update Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-06-14 14:52 ` Josh Berkus <josh@agliodbs.com>
2001-06-14 15:42 RE: search/replace in update Jeff Eckermann <jeckermann@verio.net>
2001-06-14 16:38 ` Josh Berkus <josh@agliodbs.com>
2001-06-14 17:32   ` Alex Pilosov <alex@pilosoft.com>
2001-06-15 11:30 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2001-06-15 14:25 RE: search/replace in update Jeff Eckermann <jeckermann@verio.net>

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