postgresql - replace all instances of a string within text field

Question

In postgresql, how do I replace all instances of a string within a database column?

Say I want to replace all instances of cat with dog, for example.

What's the best way to do this?

Answer 1

You want to use postgresql's replace function:

replace(string text, from text, to text)

for instance :

UPDATE <table> SET <field> = replace(<field>, 'cat', 'dog')

Be aware, though, that this will be a string-to-string replacement, so 'category' will become 'dogegory'. the regexp_replace function may help you define a stricter match pattern for what you want to replace.

Answer 2

The Regular Expression Way

If you need stricter replacement matching, PostgreSQL's regexp_replace function can match using POSIX regular expression patterns. It has the syntax regexp_replace(source, pattern, replacement [, flags ]).

I will use flags i and g for case-insensitive and global matching, respectively. I will also use \m and \M to match the beginning and the end of a word, respectively.

There are usually quite a few gotchas when performing regex replacment. Let's see how easy it is to replace a cat with a dog.

SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat', 'dog');
-->                    Cat bobdog cat cats catfish

SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat', 'dog', 'i');
-->                    dog bobcat cat cats catfish

SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat', 'dog', 'g');
-->                    Cat bobdog dog dogs dogfish

SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat', 'dog', 'gi');
-->                    dog bobdog dog dogs dogfish

SELECT regexp_replace('Cat bobcat cat cats catfish', '\mcat', 'dog', 'gi');
-->                    dog bobcat dog dogs dogfish

SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat\M', 'dog', 'gi');
-->                    dog bobdog dog cats catfish

SELECT regexp_replace('Cat bobcat cat cats catfish', '\mcat\M', 'dog', 'gi');
-->                    dog bobcat dog cats catfish

SELECT regexp_replace('Cat bobcat cat cats catfish', '\mcat(s?)\M', 'dog\1', 'gi');
-->                    dog bobcat dog dogs catfish

Even after all of that, there is at least one unresolved condition. For example, sentences that begin with "Cat" will be replaced with lower-case "dog" which break sentence capitalization.

Check out the current PostgreSQL pattern matching docs for all the details.

Update entire column with replacement text

Given my examples, maybe the safest option would be:

UPDATE table SET field = regexp_replace(field, '\mcat\M', 'dog', 'gi');

Answer 3

You can use the replace function

UPDATE your_table SET field = REPLACE(your_field, 'cat','dog')

The function definition is as follows (got from here):

replace(string text, from text, to text)

and returns the modified text. You can also check out this sql fiddle.

Answer 4

Here is an example that replaces all instances of 1 or more white space characters in a column with an underscore using regular expression -

select distinct on (pd)
regexp_replace(rndc.pd, '\\s+', '_','g') as pd
from rndc14_ndc_mstr rndc;