在postgresql中,我如何替换数据库列中的字符串的所有实例?

例如,假设我想用dog替换所有的cat实例。

最好的方法是什么?


当前回答

正则表达式

如果你需要更严格的替换匹配,PostgreSQL的regexp_replace函数可以使用POSIX正则表达式模式进行匹配。它的语法是regexp_replace(source, pattern, replacement [, flags])。

我将分别使用标志I和g进行不区分大小写和全局匹配。我还将分别使用\m和\m来匹配单词的开头和结尾。

在执行正则表达式替换时,通常有相当多的陷阱。让我们看看把猫换成狗有多简单。

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\1', 'gi');
-->                    dog bobcat dog dogs catfish

即便如此,至少还有一个问题没有解决。例如,以“Cat”开头的句子将被小写的“dog”取代,这打破了句子的大写。

查看当前的PostgreSQL模式匹配文档了解所有细节。

用替换文本更新整个列

以我的例子来看,最安全的选择可能是:

UPDATE table SET field = regexp_replace(field, '\mcat\M', 'dog', 'gi');

其他回答

下面的示例使用正则表达式-将列中1个或多个空格字符的所有实例替换为下划线

select distinct on (pd)
regexp_replace(rndc.pd, '\\s+', '_','g') as pd
from rndc14_ndc_mstr rndc;

您可以使用替换功能

UPDATE your_table SET field = REPLACE(your_field, 'cat','dog')

函数定义如下(从这里得到):

replace(string text, from text, to text)

并返回修改后的文本。您还可以查看这个sql小提琴。

正则表达式

如果你需要更严格的替换匹配,PostgreSQL的regexp_replace函数可以使用POSIX正则表达式模式进行匹配。它的语法是regexp_replace(source, pattern, replacement [, flags])。

我将分别使用标志I和g进行不区分大小写和全局匹配。我还将分别使用\m和\m来匹配单词的开头和结尾。

在执行正则表达式替换时,通常有相当多的陷阱。让我们看看把猫换成狗有多简单。

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\1', 'gi');
-->                    dog bobcat dog dogs catfish

即便如此,至少还有一个问题没有解决。例如,以“Cat”开头的句子将被小写的“dog”取代,这打破了句子的大写。

查看当前的PostgreSQL模式匹配文档了解所有细节。

用替换文本更新整个列

以我的例子来看,最安全的选择可能是:

UPDATE table SET field = regexp_replace(field, '\mcat\M', 'dog', 'gi');

你想使用postgresql的replace函数:

replace(string text, from text, to text)

例如:

UPDATE <table> SET <field> = replace(<field>, 'cat', 'dog')

但是要注意,这将是一个字符串到字符串的替换,所以'category'将变成'dogegory'。regexp_replace函数可以帮助您为要替换的内容定义更严格的匹配模式。