Tell me more ×
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.
up vote 0 down vote favorite

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

Note: If there is no string or token left in field1 after substring removal the string should not be touched.

share|improve this question

closed as too localized by dezso, StanleyJohns, Mark Storey-Smith, Max Vernon, RolandoMySQLDBA May 17 at 20:13

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 2 down vote accepted

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, '') <> '';

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.

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

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