postgresql - replace all instances of a string within text field
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
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.