pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsearch/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