>>> Karen Stone <kstone@xxxxxxxxxxxxxxx> 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
