Pattern matching and substring replacement in PostgreSQL I would like to do pattern matching and substring replacement in PostgreSQL with a "select-case-when-then-update-query".
Example:

tableA

field1               |field2          | field3
---------------------+----------------+-----------
(varchar)bla abla 123|(varchar)abla   |(varchar)123
(varchar)blub 456    |(varchar)blub   |(varchar)456


remaining string in field1 after performing the substring replacement should be "bla" in row 1 and "blub 456" in row 2 (see Note below):

tableA

field1               |field2          | field3
---------------------+----------------+-----------
(varchar)bla         |(varchar)abla   |(varchar)123
(varchar)blub 456    |(varchar)blub   |(varchar)456


So, I would like to update field1 if the following condition is true:

Pseudocode:

if string from field2 and/or string from field3  is substring of field1 
then replace substring field2, field3 from field1 if remaining string in field1 &gt; 0


Note: If there is no string or token left in field1 after substring removal the string should not be touched.
You haven't disclosed what to do with whitespaces or what to do with overlapping substrings.  Anyhow, something like this could help:

UPDATE tableA
SET field1 = replace(replace(field1, field2, ''), field3, '')
WHERE replace(replace(field1, field2, ''), field3, '') &lt;&gt; '';


There is no pattern to match, simply replace a string (in an other column) with nothing.  You don't have to care about substrings as if the value of field2 does not occur in field1 then it won't be replaced.

See this on SQLFiddle.