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 /

using an update statement. If you already have a working select then its TSQL 101 to write the update. — Mitch Wheat, Jun 12 '12 at 01:38
Possible duplicate of [postgresql - replace all instances of a string within text field](https://stackoverflow.com/questions/5060526/postgresql-replace-all-instances-of-a-string-within-text-field) — Evan Carroll, Feb 04 '18 at 04:49
you'll find the best answer here: https://stackoverflow.com/a/836090/4846859 — Biskrem Muhammad, Jan 08 '22 at 15:06

score 23 · Accepted Answer · answered Jun 12 '12 at 02:07

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.

score 4 · Answer 2 · answered Jun 12 '12 at 05:48

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.

loved the 'nooking a fire' link!! – Mitch Wheat Jun 12 '12 at 08:46 — Mitch Wheat, Jun 12 '12 at 08:46

score 0 · Answer 3 · answered Jul 25 '18 at 12:34

0

If you don't have postgres, you can export all database to a sql file, replace your string with a text editor and delete your db on your host, and re-import your new db

PS: be careful

answered Jul 25 '18 at 12:34

Alberto Perez

1,019
15
17

Replace all occurrences of a substring in a database text field

3 Answers3

Linked