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


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):


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:


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 > 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:

SET field1 = replace(replace(field1, field2, ''), field3, '')
WHERE replace(replace(field1, field2, ''), field3, '') <> '';

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.

| improve this answer | |
  • Yes, you are right. But this is great to start with. Thank you! – Tom May 14 '13 at 15:54

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