String replace using concatenated strings from various columns

Question

I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order.

The specification says:

replace(string text, from text, to text)

Example:

field1 |field2 |field3 |field4 
---------------------------+-------------+------------+---------------
(varchar)bla abla 123 ooops|(varchar)abla|(varchar)123|(varchar) ooops

The substring to replace consists of strings in field2, field3, field4 and blanks between field2 and field3, field3 and field4. In the given example: "abla 123 ooops".

So, after performing the update statement field1 should only contain the string "bla".

Note: I want to perform this on all rows and not to a particular one.

• Erwin Brandstetter: You should provide your version of Postgres with every question. You should also provide the table definition. For one, this would tell us whether your columns can be NULL.

Answer 1

Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:

UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')

concat_ws() ignores NULL values. With plain concatenation (||), one NULL field would make the whole pattern NULL. It was introduced with Postgres 9.1.

The added WHERE clause prevents empty updates. This enhances performance a lot if many rows wouldn't change anyway.