Skip site navigation (1) Skip section navigation (2)

Re: Change a character in a text field

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Karen Stone" <kstone(at)mhs(dot)mphasis(dot)com>,"Naomi Walker" <nwalker(at)mhs(dot)mphasis(dot)com>,"pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Change a character in a text field
Date: 2008-07-02 18:07:29
Message-ID: 486B7D9F.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-admin
>>> Karen Stone <kstone(at)mhs(dot)mphasis(dot)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

In response to

pgsql-admin by date

Next:From: bogdadDate: 2008-07-02 18:38:19
Subject: Postgres windows service shutdowns after start
Previous:From: Lennin CaroDate: 2008-07-02 17:36:38
Subject: Re: query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group