Re: Change a character in a text field

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