postgresql - 替换文本字段中字符串的所有实例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5060526/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgresql - replace all instances of a string within text field
提问by mark
In postgresql, how do I replace all instances of a string within a database column?
在 postgresql 中，如何替换数据库列中字符串的所有实例？
Say I want to replace all instances of catwith dog, for example.
例如，假设我想替换catwith 的所有实例dog。
What's the best way to do this?
做到这一点的最佳方法是什么？
回答by Jerome WAGNER
You want to use postgresql's replacefunction:
你想使用 postgresql 的替换功能：
replace(string text, from text, to text)
for instance :
例如 ：
UPDATE <table> SET <field> = replace(<field>, 'cat', 'dog')
Be aware, though, that this will be a string-to-string replacement, so 'category' will become 'dogegory'. the regexp_replace function may help you define a stricter match pattern for what you want to replace.
但是请注意，这将是字符串到字符串的替换，因此“类别”将变为“dogegory”。regexp_replace 函数可以帮助您为要替换的内容定义更严格的匹配模式。
回答by Clint Pachl
The Regular Expression Way
正则表达式方式
If you need stricter replacement matching, PostgreSQL's regexp_replacefunction can match using POSIX regular expression patterns. It has the syntax regexp_replace(source, pattern, replacement [, flags ]).
如果需要更严格的替换匹配，PostgreSQL 的regexp_replace函数可以使用 POSIX 正则表达式模式进行匹配。它的语法为regexp_replace(source, pattern, replacement [, flags ])。
I will use flags iand gfor case-insensitive and global matching, respectively. I will also use \mand \Mto match the beginning and the end of a word, respectively.
我将分别使用标志i和g不区分大小写和全局匹配。我还将使用\m和\M分别匹配单词的开头和结尾。
There are usually quite a few gotchas when performing regex replacment. Let's see how easy it is to replace a catwith a dog.
执行正则表达式替换时通常有很多问题。让我们看看用狗代替猫是多么容易。
SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat', 'dog');
--> Cat bobdog cat cats catfish
SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat', 'dog', 'i');
--> dog bobcat cat cats catfish
SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat', 'dog', 'g');
--> Cat bobdog dog dogs dogfish
SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat', 'dog', 'gi');
--> dog bobdog dog dogs dogfish
SELECT regexp_replace('Cat bobcat cat cats catfish', '\mcat', 'dog', 'gi');
--> dog bobcat dog dogs dogfish
SELECT regexp_replace('Cat bobcat cat cats catfish', 'cat\M', 'dog', 'gi');
--> dog bobdog dog cats catfish
SELECT regexp_replace('Cat bobcat cat cats catfish', '\mcat\M', 'dog', 'gi');
--> dog bobcat dog cats catfish
SELECT regexp_replace('Cat bobcat cat cats catfish', '\mcat(s?)\M', 'dog', 'gi');
--> dog bobcat dog dogs catfish
Even after all of that, there is at leastone unresolved condition. For example, sentences that begin with "Cat" will be replaced with lower-case "dog" which break sentence capitalization.
即使在所有这些之后，至少还有一个未解决的条件。例如，以“Cat”开头的句子将被替换为小写的“dog”，这会破坏句子大写。
Check out the current PostgreSQL pattern matchingdocs for all the details.
查看当前的 PostgreSQL模式匹配文档以获取所有详细信息。
Update entire column with replacement text
使用替换文本更新整列
Given my examples, maybe the safest option would be:
鉴于我的例子，也许最安全的选择是：
UPDATE table SET field = regexp_replace(field, '\mcat\M', 'dog', 'gi');
回答by Ciprian Mocanu
You can use the replacefunction
您可以使用该replace功能
UPDATE your_table SET field = REPLACE(your_field, 'cat','dog')
The function definition is as follows (got from here):
函数定义如下（从这里得到）：
replace(string text, from text, to text)
and returns the modified text. You can also check out this sql fiddle.
并返回修改后的文本。你也可以看看这个 sql fiddle。
回答by user1797212
Here is an example that replaces all instances of 1 or more white space characters in a column with an underscore using regular expression -
这是一个使用正则表达式用下划线替换列中 1 个或多个空白字符的所有实例的示例 -
select distinct on (pd)
regexp_replace(rndc.pd, '\s+', '_','g') as pd
from rndc14_ndc_mstr rndc;
