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开始的整个字符串。

请查看以下示例：

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_name和first_name列。我们通过提取first_name列的第一个字符来获取初始名称。

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

PostgreSQL substring example

除了 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

除了 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 函数从字符串中提取子字符串。

PostgreSQL 教程：字符串函数