postgresql - replace all instances of a string within text field


Translate

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?


All Answers
  • Translate

    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.


  • Translate

    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');
    

  • Translate

    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.


  • Translate

    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;