Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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. –  Erwin Brandstetter May 15 '13 at 10:55
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
This is great. Thank you! –  Tom May 15 '13 at 11:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.