Replace all occurrences of a substring in a database text field

Question

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 '%&#47%'

the original string example:

this is the cool mega string that contains &#47

how to replace all &#47 with / ?

The end result should be:

this is the cool mega string that contains /

Answer 1

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.

Answer 2

The answer given by @davesnitty will work, but you need to think very carefully about whether the text pattern you're replacing could appear embedded in a longer pattern you don't want to modify. Otherwise you'll find someone's nooking a fire, and that's just weird.

If possible, use a suitable dedicated tool for what you're un-escaping. Got URLEncoded text? use a url decoder. Got XML entities? Process them though an XSLT stylesheet in text mode output. etc. These are usually safer for your data than hacking it with find-and-replace, in that find and replace often has unfortunate side effects if not applied very carefully, as noted above.

It's possible you may want to use a regular expression. They are not a universal solution to all problems but are really handy for some jobs.

If you want to unconditionally replace all instances of "&#47" with "/", you don't need a regexp.

If you want to replace "&#47" but not "&#471", you might need a regexp, because you can do things like match only whole words, match various patterns, specify min/max runs of digits, etc.

In the PostgreSQL string functions and operators documentation you'll find the regexp_replace function, which will let you apply a regexp during an UPDATE statement.

To be able to say much more I'd need to know what your real data is and what you're really trying to do.