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: (view raw, whole thread or download thread mbox)
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
>>> on 
>>> a regular basis...
>> It sounds like you might want to look at the regexp_replace
>> 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
> interrogate and make the change to?
Assuming standard_conforming_strings is on, this (untested) should do
UPDATE sometable
  SET somecolumn = regexp_replace(somecolumn, '\|', 'something else',
  WHERE somecolumn ~ '\|';
If standard_conforming_strings is off, double the backslashes.

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-2017 The PostgreSQL Global Development Group