changing partial data

Started by Peter Choeover 24 years ago3 messagesgeneral

choepete@mindspring.com

over 24 years ago

is there a way in postgres to change just a part of a text data in a
column. for example if i have a column called description with the
following data:

+---------------------+
| description         |
+---------------------+
| this is a test.     |
+---------------------+

and i want to change the word test to exam, can i just replace the word
test rather than reentrying to who value of the column?

so, instead of:

update foo set description='this is a exam' where description='this is a
test';

can i do something like:

update foo replace test with exam;

peter choe

Thalis A. Kalfigopoulos

thalis@cs.pitt.edu

over 24 years ago

In reply to: Peter Choe (#1)

Re: changing partial data

UPDATE tablename SET description=(substring(description for position('test' in description))||'exam'||substring(description from position('test' in description)+char_length('test')));

Keep in mind that this will only replace the first occurence of the word 'test' in the description and that description should have 'test' in it. So better to add a WHERE condition to youw update.

cheers,
thalis

On Wed, 11 Jul 2001, Peter Choe wrote:

Show quoted text

is there a way in postgres to change just a part of a text data in a
column. for example if i have a column called description with the
following data:
+---------------------+
| description         |
+---------------------+
| this is a test.     |
+---------------------+
and i want to change the word test to exam, can i just replace the word
test rather than reentrying to who value of the column?

so, instead of:

update foo set description='this is a exam' where description='this is a
test';

can i do something like:

update foo replace test with exam;

peter choe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Jeff Eckermann

jeckermann@verio.net

over 24 years ago

In reply to: Thalis A. Kalfigopoulos (#2)

RE: changing partial data

If you think you may want to do this kind of thing again, you might consider
creating a function to make it easier. At present, there is no builtin
PostgreSQL function that will do it.
Perl is especially handy for this sort of thing:

CREATE FUNCTION s(text, text)
RETURNS text AS '
return s/@_[0]/@_[1]/;
' LANGUAGE 'plperl';

CREATE FUNCTION s(text, text, text)
RETURNS text AS '
return s/@_[0]/@_[1]/@_[2];
' LANGUAGE 'plperl';

Overloading the function in this way allows for optional flags. The effect
is to get you pretty close to the actual Perl syntax, with the same
functionality.
If you want to try this, check that you have plperl installed: check the
docs ("Procedural Languages") for more info.

Show quoted text

-----Original Message-----
From: Peter Choe [SMTP:choepete@mindspring.com]
Sent: Wednesday, July 11, 2001 12:48 PM
To: postgres
Subject: [GENERAL] changing partial data

is there a way in postgres to change just a part of a text data in a
column. for example if i have a column called description with the
following data:
+---------------------+
| description         |
+---------------------+
| this is a test.     |
+---------------------+
and i want to change the word test to exam, can i just replace the word
test rather than reentrying to who value of the column?

so, instead of:

update foo set description='this is a exam' where description='this is a
test';

can i do something like:

update foo replace test with exam;

peter choe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Import Notes

Resolved by subject fallback