Re: Change a character in a text field - Mailing list pgsql-admin

From Kevin Grittner
Subject Re: Change a character in a text field
Date
Msg-id 486B7D9F.EE98.0025.0@wicourts.gov
Whole thread Raw
In response to Re: Change a character in a text field  (Karen Stone <kstone@mhs.mphasis.com>)
List pgsql-admin
>>> Karen Stone <kstone@mhs.mphasis.com> wrote:
>>> Is there some way with a SQL state to interrogate a text field, and

>>> replace characters.
>>>
>>> For example, we would like all "|"'s to be changed to something
else,
>>> on
>>> a regular basis...
>>
>> It sounds like you might want to look at the regexp_replace
function:
>>
>>
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#F
>> UNCTIONS-POSIX-REGEXP
>>
>> Be sure to use a WHERE clause on your UPDATE with the ~ operator.
>
> Can you please provide a complete example of how to use this in the
> update command?  ie... how do we select the table/field that we want
to
> interrogate and make the change to?

Assuming standard_conforming_strings is on, this (untested) should do
it:

UPDATE sometable
  SET somecolumn = regexp_replace(somecolumn, '\|', 'something else',
'g')
  WHERE somecolumn ~ '\|';

If standard_conforming_strings is off, double the backslashes.

-Kevin

pgsql-admin by date:

Previous
From: Lennin Caro
Date:
Subject: Re: query
Next
From: bogdad
Date:
Subject: Postgres windows service shutdowns after start