Postgres Pro
Downloads
Home   >   mailing lists

Thread: changing partial data

changing partial data

From
Peter Choe
Date:
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

Re: changing partial data

From
"Thalis A. Kalfigopoulos"
Date:
UPDATE tablename SET description=(substring(description for position('test' in
description))||'exam'||substring(descriptionfrom 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
shouldhave 'test' in it. So better to add a WHERE condition to youw update. 

cheers,
thalis


On Wed, 11 Jul 2001, Peter Choe wrote:

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

RE: changing partial data

From
Jeff Eckermann
Date:
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.

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