0

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.

Community
  • 1
  • 1
Kevin
  • 327
  • 6
  • 17
  • possible duplicate of [postgresql - replace all instances of a string within text field](http://stackoverflow.com/questions/5060526/postgresql-replace-all-instances-of-a-string-within-text-field) – Norbert Apr 21 '15 at 01:44
  • Would you consider putting the translation into a two-column lookup table? – Bohemian Apr 21 '15 at 05:43

3 Answers3

1

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you! I went ahead and used the nested replace. :) – Kevin Apr 21 '15 at 06:23
  • Can you mostly eliminate the concerns by using a delimiter around the tokens to replace? If the initial input string is the result of a query, concat the delimiters in the SELECT? – grantwparks Jan 04 '21 at 23:07
  • @grantwparks: Not sure I understand. Please ask your question as new question. You can always link to this one for context. – Erwin Brandstetter Jan 05 '21 at 22:05
1

Another option is a recursive function:

CREATE OR REPLACE FUNCTION replace_recursive(search text, from_to text[][])
RETURNS TEXT LANGUAGE plpgsql AS $$
BEGIN
    IF (array_length(from_to,1) > 1) THEN
        RETURN replace_recursive(
            replace(search, from_to[1][1], from_to[1][2]),
            from_to[2:array_upper(from_to,1)]
        );
    ELSE
        RETURN replace(search, from_to[1][1], from_to[1][2]);
    END IF;
END;$$;

Result:

select replace_recursive('from1, from2, from3', array[['from1', 'to1'], ['from2', 'to2']]);

┌───────────────────┐
│ replace_recursive │
├───────────────────┤
│ to1, to2, from3   │
└───────────────────┘
Ian Timothy
  • 2,623
  • 3
  • 15
  • 10
0

You should be able to do this with nested replace():

select replace(replace(str, 'Tuesday', 'cat'), 'Monday', 'dog')

If the value is not in the string, nothing happens.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786