Thread: search/replace in update
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
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
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
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
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
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
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