Is it possible to do a "find and replace" with the following?
UPDATE __table__ SET __column__ = replace(__column__, ' ', '_');
How do I define an array of strings to be found (',', ';', ':', ' ') to replace with '_'?
2 Answers
regexp_replace() is powerful, versatile ... and slow.
Use the plain (less powerful and versatile) replace() where possible, it's faster.
For the simple case at hand (replace each single character in a list with another single character) use translate() - even simpler and faster. And also less error prone.
UPDATE tbl
SET col = translate(col, ',;: ', '____')
WHERE col <> translate(col, ',;: ', '____'); -- avoid empty updates
Only update rows that actually change. It's a common (possibly expensive) mistake to update all rows unconditionally. Details:
Note that ' ' only replaces the space character (' '), while the class shorthand \s in a regular expression matches all whitespace characters of the the character class [[:space:]].
answered Dec 29, 2014 at 22:28
Erwin Brandstetter
668k159 gold badges1.2k silver badges1.3k bronze badges
Comments
Read the section about Bracket Expressions which explains how to search for characters within a string to replace
but this should work for you
UPDATE __table__ SET __column__ = regexp_replace( __column__, E'[\\s,;:]','_','g')
1 Comment
John
This worked, thanks! I'm surprised they didn't word it to include
array at least to refer to bracket in the bracket sense though if someone asks this question they'll at least find this Q/A. Thanks for linking to the documentation too; Table 9-16 included details about \s for space.
regexp_replace'{',',':',' '}'for example without any luck, if you would please post a working answer I'd be happy to accept it.