PostgreSQL 教程: substring 或 substr 函数

九月 2, 2023

摘要:在本教程中,我们将向您介绍 PostgreSQL 的 substring 函数,该函数从字符串中提取子字符串。

PostgreSQL substring 函数简介

substring 函数返回字符串的一部分。下面说明了substring函数的语法:

SUBSTRING ( string, start_position, length )

让我们详细检查每个参数:

  • string 是一个字符串,其数据类型为 char、varchar、text 等。
  • start_position 是一个整数,指定要提取子字符串的位置。如果start_position等于零,则子字符串从字符串的第一个字符开始。start_position只能是正数。尽管在其他数据库系统(例如 MySQL)中,substring函数可以接受负数的start_position
  • length 是一个正整数,它确定要从start_position开始的字符串中提取的字符数。如果start_position加上length的和大于string中的字符数,则 substring 函数返回从start_position开始的整个字符串。长度参数是可选的。如果省略长度参数,则 substring 函数将返回从start_position开始的整个字符串。

PostgreSQL substring 示例

请查看以下示例:

SELECT
	SUBSTRING ('PostgreSQL', 1, 8); -- PostgreS
SELECT
	SUBSTRING ('PostgreSQL', 8); -- SQL

在第一个语句中,我们提取一个长度为 8 的子字符串,它从PostgreSQL字符串的第一个字符开始。我们得到结果PostgreS。见下图:

PostgreSQL substring function example

在第二条语句中,我们提取从位置 8 开始的子字符串,并省略长度参数。子字符串是从 8 开始的字符串,即SQL

PostgreSQL substring function example with optional LENGH parameter

PostgreSQL 提供了另一种 substring 函数的语法,如下所示:

substring(string from start_position for length);

在这种形式中,PostgreSQL 将三个参数合成为一个。请参见以下示例:

SELECT
	SUBSTRING ('PostgreSQL' FROM 1 FOR 8); -- PostgreS
SELECT
	SUBSTRING ('PostgreSQL' FROM 8); -- SQL

结果与第一个示例中的结果相同。

在下面的示例中,我们从customer表中查询数据。我们查询last_namefirst_name列。我们通过提取first_name列的第一个字符来获取初始名称。

SELECT
	last_name,
	SUBSTRING( first_name, 1, 1 ) AS initial
FROM
	customer
ORDER BY
	last_name;

PostgreSQL substring example

提取与 POSIX 正则表达式匹配的子字符串

除了 SQL 标准的子字符串函数之外,PostgreSQL 还允许您提取与 POSIX 正则表达式匹配的子字符串。下面说明了使用 POSIX 正则表达式的 substring 函数的语法:

SUBSTRING(string FROM pattern)

或者您可以使用以下语法:

SUBSTRING(string, pattern);

请注意,如果未找到匹配项,则 substring 函数将返回空值。如果模式包含任何括号,则 substring 函数返回与第一个带括号的子表达式匹配的文本。

以下示例从字符串中提取门牌号(最多 4 位数字,从 0 到 9):

SELECT
	SUBSTRING (
		'The house no. is 9001',
		'([0-9]{1,4})'
	) as house_no

PostgreSQL substring POSIX example

提取与 SQL 正则表达式匹配的子字符串

除了 POSIX 正则表达式模式之外,您还可以使用 SQL 正则表达式模式从字符串中提取子字符串,语法如下:

SUBSTRING(string FROM pattern FOR escape-character)

这种形式的substring函数接受三个参数:

  • string: 要提取子字符串的字符串。
  • escape-character: 转义字符。
  • pattern: 一个包含在转义字符内的正则表达式,后跟双引号 (")。例如,如果字符#是转义字符,则模式将为#"pattern#"。另外,pattern必须匹配整个string,否则,子字符串函数将失败并返回一个NULL值。

请查看以下示例:

SELECT SUBSTRING (
	'PostgreSQL'
	FROM
		'%#"S_L#"%' FOR '#'
); -- SQL

SELECT SUBSTRING (
	'foobar'
	FROM
		'#"S_Q#"%' FOR '#'
); -- NULLL

PostgreSQL 提供了另一个名为substr的函数,其功能与substring函数相同。

概括

  • 使用 PostgreSQL substring 函数从字符串中提取子字符串。