0

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 '_'?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
John
  • 1
  • 13
  • 98
  • 177

2 Answers2

3

regexp_replace() is powerful, versatile ... and slow.

If you can, use the plain (less powerful and versatile) replace(), which is much faster.

For the simple case at hand (replace a list of single characters with another single character) use translate() - even simpler and faster. And also much 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 this only replaces the space character (' ') while the class shorthand \s in a regular expression matches all whitespace characters of the the character class [[:space:]].

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

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')
NullEverything
  • 450
  • 2
  • 5
  • 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. – John Dec 29 '14 at 21:02