根据值用列表中的另一个字符串替换一个字符串 [英] Replace a string with another string from a list depending on the value
问题描述
假设我有以下字符串：‘2015/4/21（星期二）’。我想用另一个字符串替换星期二，例如：猫。结果应该是：'2015/4/21（cat）'。
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.
我如何在PostgreSQL 8.4中做到这一点？
How do I do this in PostgreSQL 8.4?
有一个类似的帖子： postgresql-替换所有实例文本字段中的字符串
但是我需要根据日期替换 dynamic 的内容，并且该帖子替换已知的值。
But mine needs to replace something dynamic depending on the day and that post replaces a known value.
推荐答案
对于几个互斥替换，嵌套的replace语句是最简单，最快的方法。只是像@Gordon所建议的那样。
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:
当字符串可以是彼此的子字符串时，这变得模棱两可。考虑以下两个表达式：
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.
然后，也有可能一个替换项可能与下一个替换项匹配：
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.
正如我所说，只有t在一周中的几天，嵌套 replace（）语句很好。这实际上不是动态的。如果工作日只是为了说明问题，而您实际上必须处理更多的案例或真正的动态字符串，那么我会考虑采用另一种方法。在此相关答案中找到完全动态解决方案：
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:
- How to replace all subsets of characters based on values of other tables in pl/pgsql?
这篇关于根据值用列表中的另一个字符串替换一个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
