Replace all occurrences of a substring in a database text field
postgresql, replace, sql
Solution
If you want to replace a specific string with another string or transformation of that string, you could use the "replace" function in postgresql. For instance, to replace all occurances of "cat" with "dog" in the column "myfield", you would do:
UPDATE tablename
SET myfield = replace(myfield,"cat", "dog")
You could add a WHERE clause or any other logic as you see fit.
Alternatively, if you are trying to convert HTML entities, ASCII characters, or between various encoding schemes, postgre has functions for that as well. Postgresql String Functions.
Problem
I have a database that has around 10k records and some of them contain HTML characters which I would like to replace. For example I can find all occurrences: ``` SELECT * FROM TABLE WHERE TEXTFIELD LIKE '%/%' ``` the original string example: `this is the cool mega string that contains /` how to replace all `/` with `/` ? The end result should be: `this is the cool mega string that contains /`