CODEWITHSUNDEEP
postgresqlpattern-matching
John
John

Reputation: 13739

Find multiple strings and replace with single string in Postgres

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

Upvotes: 0

Views: 6570

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656962

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:]].

Upvotes: 3

NullEverything
NullEverything

Reputation: 460

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')

Upvotes: 1

Related Questions