在 postgresql 中,如何替换数据库列中字符串的所有实例?
例如,假设我想用 dog
替换所有 cat
实例。
最好的方法是什么?
你想使用 postgresql 的 replace 函数:
replace(string text, from text, to text)
例如 :
UPDATE <table> SET <field> = replace(<field>, 'cat', 'dog')
但请注意,这将是字符串到字符串的替换,因此“类别”将变为“狗狗”。 regexp_replace 函数可以帮助您为要替换的内容定义更严格的匹配模式。
正则表达式方式
如果您需要更严格的替换匹配,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 pattern matching 文档以了解所有详细信息。
用替换文本更新整个列
鉴于我的例子,也许最安全的选择是:
UPDATE table SET field = regexp_replace(field, '\mcat\M', 'dog', 'gi');
^
和 $
匹配 string 的开头和结尾;但 \m
和 \M
仅匹配 word 的开头和结尾。因此,在大多数情况下,使用 ^
和 $
会失败。
您可以使用 replace
功能
UPDATE your_table SET field = REPLACE(your_field, 'cat','dog')
函数定义如下(取自here):
replace(string text, from text, to text)
并返回修改后的文本。您也可以查看this sql fiddle。
这是一个示例,它使用正则表达式将列中 1 个或多个空白字符的所有实例替换为下划线 -
select distinct on (pd)
regexp_replace(rndc.pd, '\\s+', '_','g') as pd
from rndc14_ndc_mstr rndc;
regexp_replace