PostgreSQL 教程: replace、regexp_replace、translate 字符串替换函数

九月 2, 2023

摘要:在本教程中,我们将向您介绍 PostgreSQL 的字符串替换函数,这些函数在字符串中搜索子字符串并将其替换为新的子字符串。

PostgreSQL REPLACE 函数

有时,您想要搜索列中的字符串并将其替换为新字符串,例如替换过时的电话号码、损坏的 URL 和拼写错误。

要搜索所有出现的字符串并将其替换为新字符串,请使用REPLACE()函数。

下面说明了 PostgreSQL 的REPLACE()函数的语法:

REPLACE(source, old_text, new_text );

REPLACE()函数接受三个参数:

  • source是您要替换的字符串。
  • old_text是您要搜索和替换的文本。如果old_text在字符串中多次出现,则所有出现的位置都将被替换。
  • new_text是将替换旧文本 (old_text) 的新文本。

PostgreSQL REPLACE() 函数示例

请查看以下使用REPLACE()函数的示例:

SELECT
	REPLACE ('ABC AA', 'A', 'Z');

PostgreSQL REPLACE example

在此示例中,我们将字符串中的所有字符 ‘A’ 替换为字符 ‘Z’。

以下示例将 URL 中的子字符串tt替换为xx

SELECT
	REPLACE (
		'https://www.rockdata.net',
		'tt',
		'xx'
	);

PostgreSQL REPLACE example 2

如果要搜索并替换表列中的子字符串,请使用以下语法:

UPDATE
   table_name
SET
   column_name = REPLACE(column, old_text, new_text)
WHERE
   condition

我们使用示例数据库中的customer表进行演示:

SELECT
	first_name,
	last_name,
	email
FROM
	customer;

customer table

现在,假设您要更新电子邮件列以将域名sakilacustomer.org替换为rockdata.net,请使用以下语句:

UPDATE 
   customer
SET 
   email = REPLACE (
  	email,
	'sakilacustomer.org',
	'rockdata.net'
   );

因为我们省略了WHERE子句,所以customer表中的所有行都被更新。

让我们验证一下是否已进行替换。

SELECT
	first_name,
	last_name,
	email
FROM
	customer;

postgresql replace in database

PostgreSQL REGEXP_REPLACE 函数

如果您需要更高级的匹配,可以使用REGEXP_REPLACE()函数。

REGEXP_REPLACE()函数允许您替换与正则表达式匹配的子字符串。下面说明了REGEX_REPLACE()函数的语法。

REGEXP_REPLACE(source, pattern, new_text [,flags])

REGEXP_REPLACE()函数接受四个参数:

  • source是您将查找与pattern匹配的子字符串并将其替换为new_text的字符串。如果未找到匹配项,则来源不变。
  • pattern是一个正则表达式。它可以是任何模式,例如:电子邮件、URL、电话号码等。
  • next_text是替换子字符串的文本。
  • flags包含零个或多个控制REGEX_REPLACE()函数行为的单字母标志。例如,i表示不区分大小写匹配或忽略大小写。g 代表全局;如果使用 g 标志,该函数将替换所有与该模式匹配的子字符串。flags参数是可选的。

PostgreSQL REGEXP_REPLACE() 函数示例

需要努力和实验才能理解REGEXP_REPLACE()函数是如何工作的。

以下是使用REGEXP_REPLACE()函数的示例。

SELECT
	regexp_replace(
		'foo bar foobar barfoo',
		'foo',
		'bar'
	);
bar bar foobar barfoo

在下面的示例中,因为我们使用了i标志,所以它会忽略大小写并将第一次出现的Barbar替换为foo

SELECT
	regexp_replace(
		'Bar foobar bar bars',
		'Bar',
		'foo',
		'i'
	);
foo foobar bar bars

在下面的示例中,我们使用g标志,所有出现的bar都被替换为foo。请注意BarBARbAR不会改变。

SELECT
	regexp_replace(
		'Bar sheepbar bar bars barsheep',
		'bar',
		'foo',
		'g'
	);
Bar sheepfoo foo foos foosheep

在下面的示例中,我们同时使用gi标志,因此所有出现的barBarBAR等都将替换为foo

SELECT
	regexp_replace(
		'Bar sheepbar bar bars barsheep',
		'bar',
		'foo',
		'gi'
	);
foo sheepfoo foo foos foosheep

\m表示仅在每个单词的开头匹配。无论大小写以bar开头的所有单词都将替换为foo。以bar结尾的单词不会改变。

SELECT
	regexp_replace(
		'Bar sheepbar bar bars barsheep',
		'\mbar',
		'foo',
		'gi'
	);
foo sheepbar foo foos foosheep

\M表示仅在每个单词的末尾匹配。任何情况所有以bar结尾的单词都被替换为foo。以bar开头的单词不会被替换。

SELECT
	regexp_replace(
		'Bar sheepbar bar bars barsheep',
		'bar\M',
		'foo',
		'gi'
	);
foo sheepfoo foo bars barsheep

\m\M表示每个单词的开头和结尾都匹配。所有以bar开头和结尾的单词在任何情况下都被替换为foo

SELECT
	regexp_replace(
		'Bar sheepbar bar bars barsheep',
		'\mbar\M',
		'foo',
		'gi'
	);
foo sheepbar foo bars barsheep

PostgreSQL TRANSLATE 函数

除了REPLACE()REGEXP_REPLACE()函数之外,PostgreSQL 还提供了另一个名为TRANSLATE()的字符串替换函数。

给定一个组字符setTRANSLATE()函数将source字符串中与set匹配的任何字符替换为new_set中的字符。

TRANSLATE(source, set, new_set);

TRANSLATE()函数接受三个参数:

  • source是您要搜索和替换的字符串。
  • set是用于匹配的一组字符。
  • new_set是替换匹配set的字符的一组字符。

请注意,如果set的字符数多于new_set,PostgreSQL 会从source字符串中删除set里面的多余字符。

PostgreSQL TRANSLATE 示例

在下面的示例中,我们将所有特殊元音翻译为普通元音。

SELECT
	TRANSLATE (
		'LÒ BÓ VÔ XÕ',
		'ÒÓÔÕ',
		'OOOO'
	);
LO BO VO XO

参见下图。

postgresql translate function

在本教程中,我们向您展示了各种字符串替换函数:REPLACE()REGEXP_REPLACE()TRANSLATE(),搜索子字符串并将其替换为新子字符串。