Postgresql – String replace using concatenated strings from various columns

pattern matchingpostgresqlupdate

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.

Best Answer

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.

Related Solutions

Postgresql – Parse hyphenated word to unsigned integer

I think it would be better to work around your problem: simply replace hyphens with spaces in the strings to be ts_vectored. If necessary, you can store the unhyphenated values in a separate column and run your searches against it.

Sql-server – updating the entire column with datetime values based on other values in the row

It looks like you have at least two different regional date formats there, possibly USA and Europe? You could just split the updates by languages...

something like this, which uses the language setting to control isdate and convert.

set language english;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

set language british;
update test_dates 
set ToDateTime = convert(datetime,ContainedData)
where AID='123' and PID='2' and isdate(ContainedData)=1;

If the table is too large to update in big chunks, then you should look at wrapping that in a loop with a row limit and a transaction around each update and repeat until zero rows have been updated.

Best Answer

Related Solutions

Postgresql – Parse hyphenated word to unsigned integer

Sql-server – updating the entire column with datetime values based on other values in the row

Related Question