PostgreSQL - 如何使用正则表达式模式从字符串中提取子字符串的第一次出现？

Question

我正在尝试使用正则表达式从文本列中提取子字符串，但在某些情况下，该字符串中有多个该子字符串的实例。

在这些情况下，我发现查询结果并不返回该子字符串的第一个出现位置。有没有人知道我做错了什么？

例如：

如果我有以下数据：

create table data1
(full_text text, name text);

insert into data1 (full_text)
values ('I 56, donkey, moon, I 92')

我正在使用：

UPDATE data1
SET name = substring(full_text from '%#"I ([0-9]{1,3})#"%' for '#')

我想得到'I 56'而不是'I 92'

- Michelle

---

请添加表的定义（作为“创建表”），一些示例数据（最好作为“插入”语句）以及基于该数据的预期输出（请使用格式化文本，不要使用屏幕截图）。 - user330315

请编辑您的问题。不要在评论中发布代码。 - user330315

split_part(yourColumn, 'delimiter', 1) - Mihai

Answer 1

您可以使用regexp_matches()代替：

update data1
  set full_text = (regexp_matches(full_text, 'I [0-9]{1,3}'))[1];

由于没有传递额外的标志，regexp_matches() 只返回第一次匹配 - 但它返回一个数组，因此您需要从结果中选择第一个（也是唯一的）元素（即 [1] 部分）

将更新限制在仅匹配正则表达式的行可能是个好主意：

update data1
  set full_text = (regexp_matches(full_text, 'I [0-9]{1,3}'))[1]
where full_text ~ 'I [0-9]{1,3}'

- user330315

---

我觉得regexp_matches()的默认实现有点奇怪，它会返回一个只有一个元素的数组...他们应该添加一个regexp_match()函数。 - gilad905

在我遇到这个问题时，我非常困惑为什么我的数组访问不起作用。我发现这是一个相关的问题/答案：为什么在访问由函数返回的数组时需要添加括号。 - World Outsider

我有一个性能问题，因为regex_match在整个文本中搜索可能的匹配项，所以正在寻找叙述中的模式。我只需要它获取通常位于叙述开头的第一个匹配项并立即退出，这样它可以更有效地浏览数千条记录。 - Guasqueño

也许在Postgresql 15版本中，regexp_substr()函数可以完成仅获取第一个的工作。 - Guasqueño

Answer 2

尝试以下表达式。它将返回第一个匹配项：

SUBSTRING(full_text, 'I [0-9]{1,3}')

- Guasqueño

Answer 3

在 PostgreSQL 10+ 中，您可以使用 regexp_match()

select regexp_match('I 56, donkey, moon, I 92', 'I [0-9]{1,3}');

来自文档的引用:

在大多数情况下，应该使用带有 g 标志的 regexp_matches()， 因为如果您只想要第一个匹配项，则使用 regexp_match() 更容易和更高效。 然而，regexp_match() 仅存在于 PostgreSQL 版本10及以上。 在旧版本中工作时，常见的技巧是将 regexp_matches() 调用放在子查询中...

- jian