pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSearch and Replace
11+ messages / 6 participants
[nested] [flat]
* Search and Replace
@ 2003-01-08 14:02 Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-08 14:56 ` Re: Search and Replace Bruno Wolff III <bruno@wolff.to>
2003-01-08 15:37 ` Re: Search and Replace Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-01-08 16:18 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 3 replies; 11+ messages in thread
From: Randy D. McCracken @ 2003-01-08 14:02 UTC (permalink / raw)
To: pgsql-sql
I apologize for having to resort to sending what is most likely a simple
tech support question regarding PostgreSQL to this list but I have not
been able to find the answer in the documentation.
I am responsible for managing a database containing over 6,000 records of
US Forest Service Research publications
(http://www.srs.fs.usda.gov/pubs/search.jsp) and I need to do a simple
search and replace in one of the columns. In these records we have a
field for URLs of the location the research publications and I need to
change the base URL from www.srs.fs.fed.us to www.srs.fs.usda.gov. It
seems like this "search and replace" would be a simple thing to do with an
UPDATE command but I am having great difficulty making this work.
The table definition I am trying to update is: url. At first I thought
about using a simple UPDATE command like this:
UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url =
'www.srs.fs.fed.us';
Of course that would work fine but "www.srs.fs.fed.us" is only part of a
complete URL such as: http://www.srs.fs.fed.us/pubs/rp/rp_se263.pdf. My
problem (among other things!) is that I don't know how to pass along
wildcards so that I do not change the other parts of the complete URL. I
have tried substituting "like" for "=" and trying to use the wildcard of
"%" but to no avail. I am really just guessing here.
Any help would be greatly appreciated!
Best,
--rdm
=======================================
Randy D. McCracken (0>
Web Guy //\
Communications Group V_/_
USDA/FS - Southern Research Station
E-Mail: rdm@srs.fs.usda.gov
Voice: (828) 259-0518
Fax: (828) 257-4840
Web: http://www.srs.fs.fed.us/
=======================================
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Search and Replace
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
@ 2003-01-08 14:56 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 11+ messages in thread
From: Bruno Wolff III @ 2003-01-08 14:56 UTC (permalink / raw)
To: Randy D. McCracken <rdm@srs.fs.usda.gov>; +Cc: pgsql-sql
On Wed, Jan 08, 2003 at 09:02:47 -0500,
"Randy D. McCracken" <rdm@srs.fs.usda.gov> wrote:
>
> UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url =
> 'www.srs.fs.fed.us';
For simple cases you can do this with available string functions.
For more complicated cases, you can write a simple application
that selects records (which means you will need a unique key returned)
based on a regular expression and then the application will issue
updates for the matching records.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Search and Replace
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
@ 2003-01-08 15:37 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2 siblings, 0 replies; 11+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-01-08 15:37 UTC (permalink / raw)
To: Randy D. McCracken <rdm@srs.fs.usda.gov>; pgsql-sql
REPLACE is the right function for you avaliable as an addon.
install it in postgresql installation using the source at:
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23
if you face problem please lemme know
once this function is installed you could update like:
--------------
UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , 'www.srs.fs.usda.gov' , url )
WHERE url ilike '%www.srs.fs.fed.us%';
--------------
regds
mallah.
On Wednesday 08 January 2003 07:32 pm, Randy D. McCracken wrote:
> I apologize for having to resort to sending what is most likely a simple
> tech support question regarding PostgreSQL to this list but I have not
> been able to find the answer in the documentation.
>
> I am responsible for managing a database containing over 6,000 records of
> US Forest Service Research publications
> (http://www.srs.fs.usda.gov/pubs/search.jsp) and I need to do a simple
> search and replace in one of the columns. In these records we have a
> field for URLs of the location the research publications and I need to
> change the base URL from www.srs.fs.fed.us to www.srs.fs.usda.gov. It
> seems like this "search and replace" would be a simple thing to do with an
> UPDATE command but I am having great difficulty making this work.
>
> The table definition I am trying to update is: url. At first I thought
> about using a simple UPDATE command like this:
>
> UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url =
> 'www.srs.fs.fed.us';
>
> Of course that would work fine but "www.srs.fs.fed.us" is only part of a
> complete URL such as: http://www.srs.fs.fed.us/pubs/rp/rp_se263.pdf. My
> problem (among other things!) is that I don't know how to pass along
> wildcards so that I do not change the other parts of the complete URL. I
> have tried substituting "like" for "=" and trying to use the wildcard of
> "%" but to no avail. I am really just guessing here.
>
> Any help would be greatly appreciated!
>
> Best,
>
> --rdm
>
>
> =======================================
> Randy D. McCracken (0>
> Web Guy //\
> Communications Group V_/_
>
> USDA/FS - Southern Research Station
>
> E-Mail: rdm@srs.fs.usda.gov
> Voice: (828) 259-0518
> Fax: (828) 257-4840
> Web: http://www.srs.fs.fed.us/
> =======================================
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Search and Replace
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
@ 2003-01-08 16:18 ` Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-08 20:18 ` Re: [SQL] Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2 siblings, 1 reply; 11+ messages in thread
From: Ross J. Reedstrom @ 2003-01-08 16:18 UTC (permalink / raw)
To: Randy D. McCracken <rdm@srs.fs.usda.gov>; +Cc: pgsql-sql
On Wed, Jan 08, 2003 at 09:02:47AM -0500, Randy D. McCracken wrote:
> I apologize for having to resort to sending what is most likely a simple
> tech support question regarding PostgreSQL to this list but I have not
> been able to find the answer in the documentation.
Hey, this is Open Source: that's what the mailing lists are for. The only
concern would be is this the right list? I'd suggest that this should probably
be over in NOVICE, but at least you didn't post to HACKERS ;-)
<snip description of needing a simple string replace>
As you've discovered, standard SQL text processing functions are a bit
primitive - usually you break out to the application language for that
sort of thing. However, if you know for sure that there's only one
instance of the replace string, and it's a fixed length string, you
can get away with something like this:
test=# select * from pubs;
id | url
----+--------------------------------
1 | http://www.srs.fs.fed.us/pub/1
2 | http://www.srs.fs.fed.us/pub/2
3 | http://www.srs.fs.fed.us/pub/3
(3 rows)
test=# update pubs set url= substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||substr(url,strpos(url,'www.srs.fs.fed.us')+17);
UPDATE 3
test=# select * from pubs;
id | url
----+----------------------------------
1 | http://www.srs.fs.usda.gov/pub/1
2 | http://www.srs.fs.usda.gov/pub/2
3 | http://www.srs.fs.usda.gov/pub/3
(3 rows)
You can figure out how it works by playing with SELECTing different substr() ans strpos() directly, like this excerpt from my query history:
select strpos(url,'www.srs.fs.usda.gov') from pubs;
select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs;
select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs;
select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs;
select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs;
Hope this helps,
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Research Scientist phone: 713-348-6166
The Connexions Project http://cnx./rice.edu fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Search and Replace
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-08 16:18 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
@ 2003-01-08 20:18 ` Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-09 05:30 ` Re: Search and Replace Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 1 reply; 11+ messages in thread
From: Randy D. McCracken @ 2003-01-08 20:18 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-novice@postgresql.org
Just to close off another thread and to give a tad more information...
I was not clear enough in my initial question to the list because not all
of the URLs I am trying to change begin with http://www.srs.fs.fed.us/ so
what I was really looking for was the syntax for replacing
"www.srs.fs.fed.us" with "www.srs.fs.usda.gov" and not touching any
records do not contain "www.srs.fs.fed.us"
Ross Reedstrom was kind enough to give me some additional help that worked
perfectly and after doing a few tests I am happy to share his SQL
statement with the list.
update pubs set
url=substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||substr(url,strpos(url,'www.srs.fs.fed.us')+17)
where url ~ 'www.srs.fs.fed.us'
Thanks Ross!
--rdm
On Wed, 8 Jan 2003, Ross J. Reedstrom wrote:
>
> <snip description of needing a simple string replace>
>
> As you've discovered, standard SQL text processing functions are a bit
> primitive - usually you break out to the application language for that
> sort of thing. However, if you know for sure that there's only one
> instance of the replace string, and it's a fixed length string, you
> can get away with something like this:
>
>
> test=# select * from pubs;
> id | url
> ----+--------------------------------
> 1 | http://www.srs.fs.fed.us/pub/1
> 2 | http://www.srs.fs.fed.us/pub/2
> 3 | http://www.srs.fs.fed.us/pub/3
> (3 rows)
>
> test=# update pubs set url=
>
> substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||substr(url,strpos(url,'www.srs.fs.fed.us')+17);
>
> UPDATE 3
>
> test=# select * from pubs;
> id | url
> ----+----------------------------------
> 1 | http://www.srs.fs.usda.gov/pub/1
> 2 | http://www.srs.fs.usda.gov/pub/2
> 3 | http://www.srs.fs.usda.gov/pub/3
> (3 rows)
>
> You can figure out how it works by playing with SELECTing different
> substr() ans strpos() directly, like this excerpt from my query history:
>
> select strpos(url,'www.srs.fs.usda.gov') from pubs;
> select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs;
> select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs;
> select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs;
>
> Hope this helps,
>
> Ross
> --
> Ross Reedstrom, Ph.D. reedstrm@rice.edu
> Research Scientist phone: 713-348-6166
> The Connexions Project http://cnx./rice.edu fax: 713-348-6182
> Rice University MS-39
> Houston, TX 77005
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Search and Replace
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-08 16:18 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-08 20:18 ` Re: [SQL] Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
@ 2003-01-09 05:30 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-01-09 23:03 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 11+ messages in thread
From: Rajesh Kumar Mallah. @ 2003-01-09 05:30 UTC (permalink / raw)
To: Randy D. McCracken <rdm@srs.fs.usda.gov>; pgsql-sql; +Cc: pgsql-novice@postgresql.org
any anyone explain whats wrong with the replace based solution to this problem
which i posted earlier?
did i misunderstood anything?
regds
mallah.
On Thursday 09 January 2003 01:48 am, Randy D. McCracken wrote:
> Just to close off another thread and to give a tad more information...
>
> I was not clear enough in my initial question to the list because not all
> of the URLs I am trying to change begin with http://www.srs.fs.fed.us/ so
> what I was really looking for was the syntax for replacing
> "www.srs.fs.fed.us" with "www.srs.fs.usda.gov" and not touching any
> records do not contain "www.srs.fs.fed.us"
>
> Ross Reedstrom was kind enough to give me some additional help that worked
> perfectly and after doing a few tests I am happy to share his SQL
> statement with the list.
>
> update pubs set
> url=substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||s
>ubstr(url,strpos(url,'www.srs.fs.fed.us')+17) where url ~
> 'www.srs.fs.fed.us'
>
> Thanks Ross!
>
> --rdm
>
> On Wed, 8 Jan 2003, Ross J. Reedstrom wrote:
> > <snip description of needing a simple string replace>
> >
> > As you've discovered, standard SQL text processing functions are a bit
> > primitive - usually you break out to the application language for that
> > sort of thing. However, if you know for sure that there's only one
> > instance of the replace string, and it's a fixed length string, you
> > can get away with something like this:
> >
> >
> > test=# select * from pubs;
> > id | url
> > ----+--------------------------------
> > 1 | http://www.srs.fs.fed.us/pub/1
> > 2 | http://www.srs.fs.fed.us/pub/2
> > 3 | http://www.srs.fs.fed.us/pub/3
> > (3 rows)
> >
> > test=# update pubs set url=
> >
> > substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||sub
> >str(url,strpos(url,'www.srs.fs.fed.us')+17);
> >
> > UPDATE 3
> >
> > test=# select * from pubs;
> > id | url
> > ----+----------------------------------
> > 1 | http://www.srs.fs.usda.gov/pub/1
> > 2 | http://www.srs.fs.usda.gov/pub/2
> > 3 | http://www.srs.fs.usda.gov/pub/3
> > (3 rows)
> >
> > You can figure out how it works by playing with SELECTing different
> > substr() ans strpos() directly, like this excerpt from my query history:
> >
> > select strpos(url,'www.srs.fs.usda.gov') from pubs;
> > select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> > select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> > select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> > select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> > select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs;
> > select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs;
> > select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs;
> > select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs;
> >
> > Hope this helps,
> >
> > Ross
> > --
> > Ross Reedstrom, Ph.D. reedstrm@rice.edu
> > Research Scientist phone: 713-348-6166
> > The Connexions Project http://cnx./rice.edu fax: 713-348-6182
> > Rice University MS-39
> > Houston, TX 77005
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Search and Replace
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-08 16:18 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-08 20:18 ` Re: [SQL] Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-09 05:30 ` Re: Search and Replace Rajesh Kumar Mallah. <mallah@trade-india.com>
@ 2003-01-09 23:03 ` Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-10 00:15 ` Re: [SQL] Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
0 siblings, 1 reply; 11+ messages in thread
From: Ross J. Reedstrom @ 2003-01-09 23:03 UTC (permalink / raw)
To: Rajesh Kumar Mallah. <mallah@trade-india.com>; +Cc: Randy D. McCracken <rdm@srs.fs.usda.gov>; pgsql-sql; pgsql-novice@postgresql.org
On Thu, Jan 09, 2003 at 11:00:32AM +0530, Rajesh Kumar Mallah. wrote:
>
> any anyone explain whats wrong with the replace based solution to this problem
> which i posted earlier?
>
> did i misunderstood anything?
Probably just overkill - I'm sure it would work, but, based on how the
question was asked, I guessed that the original questioner was looking
for a quick, onetime fix sort of thing, and wasn't real comfortable with
SQL, let alone adding contrib extension products to the installation.
Ross
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Search and Replace
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-08 16:18 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-08 20:18 ` Re: [SQL] Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-09 05:30 ` Re: Search and Replace Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-01-09 23:03 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
@ 2003-01-10 00:15 ` Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-10 00:38 ` Re: [SQL] Search and Replace Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 11+ messages in thread
From: Randy D. McCracken @ 2003-01-10 00:15 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@rice.edu>; +Cc: Rajesh Kumar Mallah. <mallah@trade-india.com>; pgsql-sql; pgsql-novice@postgresql.org
On Thu, 9 Jan 2003, Ross J. Reedstrom wrote:
> On Thu, Jan 09, 2003 at 11:00:32AM +0530, Rajesh Kumar Mallah. wrote:
> >
> > any anyone explain whats wrong with the replace based solution to this problem
> > which i posted earlier?
> >
> > did i misunderstood anything?
>
> Probably just overkill - I'm sure it would work, but, based on how the
> question was asked, I guessed that the original questioner was looking
> for a quick, onetime fix sort of thing, and wasn't real comfortable with
> SQL, let alone adding contrib extension products to the installation.
>
> Ross
>
>
Hi Rajesh,
I don't know why but the example you gave me did not work. Here is what
happened when I tried:
==========
pubs_test=# UPDATE publications SET url = replace( 'www.srs.fs.fed.us' ,
'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%';
ERROR: Function 'replace(unknown, unknown, text)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
==========
Obviously I am a newbie at PostgreSQL (I have also joined the Novice
mailing list to learn more) so I can not tell you why your example did not
work, perhaps someone else with more experience can add some information.
Best,
--rdm
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: [SQL] Search and Replace
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-08 16:18 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-08 20:18 ` Re: [SQL] Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-09 05:30 ` Re: Search and Replace Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-01-09 23:03 ` Re: Search and Replace Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-10 00:15 ` Re: [SQL] Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
@ 2003-01-10 00:38 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 11+ messages in thread
From: Bruno Wolff III @ 2003-01-10 00:38 UTC (permalink / raw)
To: Randy D. McCracken <rdm@srs.fs.usda.gov>; +Cc: Ross J. Reedstrom <reedstrm@rice.edu>; Rajesh Kumar Mallah. <mallah@trade-india.com>; pgsql-sql; pgsql-novice@postgresql.org
On Thu, Jan 09, 2003 at 19:15:51 -0500,
"Randy D. McCracken" <rdm@srs.fs.usda.gov> wrote:
>
> Hi Rajesh,
>
> I don't know why but the example you gave me did not work. Here is what
> happened when I tried:
>
> ==========
>
> pubs_test=# UPDATE publications SET url = replace( 'www.srs.fs.fed.us' ,
> 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%';
>
> ERROR: Function 'replace(unknown, unknown, text)' does not exist
> Unable to identify a function that satisfies the given argument types
> You may need to add explicit typecasts
The arguments to replace were in the wrong order.
^ permalink raw reply [nested|flat] 11+ messages in thread
* search and replace
@ 2004-06-09 17:30 Jodi Kanter <jkanter@virginia.edu>
2004-06-11 08:16 ` Re: search and replace Benoît BOURNON <benoit.bournon@adelis.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jodi Kanter @ 2004-06-09 17:30 UTC (permalink / raw)
To: pgsql-sql
I have a field in one of my tables that has a path to a file listed. I
need to move those files and now have to update all those paths. Is
there a simply search and replace type option in sql? or do I need to do
an update using the entire new path name?
If so, I think the easier solution will be to dump the table, use a
macro to update it and then restore.
Jodi
--
/_______________________________
//Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu <mailto:jkanter@virginia.edu>/
/ /
/ /
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: search and replace
2004-06-09 17:30 search and replace Jodi Kanter <jkanter@virginia.edu>
@ 2004-06-11 08:16 ` Benoît BOURNON <benoit.bournon@adelis.com>
0 siblings, 0 replies; 11+ messages in thread
From: Benoît BOURNON @ 2004-06-11 08:16 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-sql
I do not try,
may be it exists different languages to make this in postgresql :
I make this in pl/pgsql, I do not have time to make this in C,
CREATE OR REPLACE FUNCTION "_replaceblock" (text, text, text) RETURNS
text AS'
DECLARE
text_begin ALIAS FOR $1 ;
key_name ALIAS FOR $2 ;
key_val ALIAS FOR $3 ;
key_begin VARCHAR(10) := '{' ;
key_end VARCHAR(10) := '}' ;
return_text TEXT ;
return_text_length INTEGER;
find_key_start INTEGER ;
find_key_end INTEGER ;
temp_text TEXT ;
key_search TEXT ;
key_value TEXT ;
BEGIN
IF (key_val IS NULL) THEN
key_value :='' ;
ELSE
key_value := key_val ;
END IF ;
return_text := text_begin ;
key_search := key_begin || key_name || key_end ;
find_key_start := position (key_search IN return_text) ;
WHILE (find_key_start >0) LOOP
IF (find_key_start > 0) THEN
find_key_end := find_key_start + length (key_search) ;
find_key_start := find_key_start - 1 ;
return_text_length := length (return_text) ;
temp_text := substring (return_text FROM 1 FOR find_key_start) ;
temp_text := temp_text || key_value ;
temp_text := temp_text || substring (return_text FROM find_key_end) ;
return_text := temp_text ;
END IF ;
find_key_start := position (key_search IN return_text) ;
END LOOP ;
return return_text ;
END ;
'LANGUAGE 'plpgsql';
I do not know it exists a contrib to use perl in procedure language ???
... but you can use TCL if you knows this language.
Jodi Kanter wrote:
> I have a field in one of my tables that has a path to a file listed. I
> need to move those files and now have to update all those paths. Is
> there a simply search and replace type option in sql? or do I need to
> do an update using the entire new path name?
> If so, I think the easier solution will be to dump the table, use a
> macro to update it and then restore.
> Jodi
> --
>
> /_______________________________
> //Jodi L Kanter
> BioInformatics Database Administrator
> University of Virginia
> (434) 924-2846
> jkanter@virginia.edu <mailto:jkanter@virginia.edu>/
>
>
>
> / /
>
> / /
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2004-06-11 08:16 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-08 14:02 Search and Replace Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-08 14:56 ` Bruno Wolff III <bruno@wolff.to>
2003-01-08 15:37 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-01-08 16:18 ` Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-08 20:18 ` Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-09 05:30 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2003-01-09 23:03 ` Ross J. Reedstrom <reedstrm@rice.edu>
2003-01-10 00:15 ` Randy D. McCracken <rdm@srs.fs.usda.gov>
2003-01-10 00:38 ` Bruno Wolff III <bruno@wolff.to>
2004-06-09 17:30 search and replace Jodi Kanter <jkanter@virginia.edu>
2004-06-11 08:16 ` Re: search and replace Benoît BOURNON <benoit.bournon@adelis.com>
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