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

User · Answer

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

User · Answer

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

User · Answer

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    -- gt                     Cat bobdog cat cats catfish  SELECT regexp replace  Cat bobcat cat cats catfish    cat    dog    i    -- gt                     dog bobcat cat cats catfish  SELECT regexp replace  Cat bobcat cat cats catfish    cat    dog    g    -- gt                     Cat bobdog dog dogs dogfish  SELECT regexp replace  Cat bobcat cat cats catfish    cat    dog    gi    -- gt                     dog bobdog dog dogs dogfish  SELECT regexp replace  Cat bobcat cat cats catfish     mcat    dog    gi    -- gt                     dog bobcat dog dogs dogfish  SELECT regexp replace  Cat bobcat cat cats catfish    cat M    dog    gi    -- gt                     dog bobdog dog cats catfish  SELECT regexp replace  Cat bobcat cat cats catfish     mcat M    dog    gi    -- gt                     dog bobcat dog cats catfish  SELECT regexp replace  Cat bobcat cat cats catfish     mcat s   M    dog 1    gi    -- gt                     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

User · Answer

You want to use postgresql s replace function   replace string text  from text  to text    for instance    UPDATE  lt table gt  SET  lt field gt    replace  lt field gt    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

[postgresql] postgresql - replace all instances of a string within text field

Examples related to postgresql

Examples related to replace