All we need is an easy explanation of the problem, so here it is.
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0