Search code examples
postgresqlreplacepostgresql-8.4

Replace a string with another string from a list depending on the value

Let's say I have this string: '2015/4/21 (Tuesday)'. I want to replace 'Tuesday' with another string, for example: 'cat'. The result should be: '2015/4/21 (cat)'.

But I also want it to be dynamic, if it's 'Tuesday', then 'cat'. If it's 'Monday', then it's dog, etc.

How do I do this in PostgreSQL 8.4?

There is a similar post: postgresql - replace all instances of a string within text field

But mine needs to replace something dynamic depending on the day and that post replaces a known value.

Solution

  • For a couple of mutually exclusive replacements, nested replace statements are the simplest and fastest way. Just like @Gordon suggests.

    But that does not scale well for more than a few replacements and there are pitfalls:

    Substrings

    It becomes ambiguous when strings can be substrings of each other. Consider these two expressions:

    SELECT replace((replace('2015 (Monday)', 'day', 'snake'), 'Monday', 'dog')
     , replace((replace('2015 (Monday)', 'Monday', 'dog'), 'day', 'snake');

    The result depends on the sequence of replacements. You have to define priorities. Typically you would replace longer strings first.

    Chains

    Then there is also the possibility that one replacement might create a match for the next:

    SELECT replace((replace('2015 (Sunday)', 'Sun', 'Mon'), 'Monday', 'dog')
     , replace((replace('2015 (Sunday)', 'Monday', 'dog'), 'Sun', 'Mon');

    Again, you have to define priorities.
    Every replacement possibly influences the next. With more than a few replacements, this becomes murky and error prone quickly. Also very hard to maintain if replacements can change.

    As I said, with just the days of the week, nested replace() statements are fine. That's not actually "dynamic". If weekdays were just to illustrate the problem and you actually have to deal with more cases or truly dynamic strings, I would consider a different approach. Find completely dynamic solutions in this related answer: