PostgreSQL从指定字符串提取整数失败，寻求解决方法

你尝试从字符串'$50M to $100M'中提取上下限整数，但执行原SQL后返回空值，期望得到lower_bound=50和upper_bound=100的结果。

原语句问题分析 #

REGEXP_MATCHES函数返回的是**setof text[]（文本数组的集合）**，在SELECT列表中直接调用两次时，PostgreSQL无法正确关联两次调用的结果，导致返回空值。

解决方案 #

方案1：使用SUBSTRING直接提取捕获组

SUBSTRING支持通过正则捕获组提取内容，可指定捕获组索引获取对应值：

SELECT
  SUBSTRING('$50M to $100M' FROM '^\\$(\\d+)M to \\$(\\d+)M$')::integer AS lower_bound,
  SUBSTRING('$50M to $100M' FROM '^\\$(\\d+)M to \\$(\\d+)M$', 2)::integer AS upper_bound;

或者针对前后数值分别匹配：

SELECT
  SUBSTRING('$50M to $100M' FROM '\\$(\\d+)M')::integer AS lower_bound,
  SUBSTRING('$50M to $100M' FROM '\\$(\\d+)M$')::integer AS upper_bound;

方案2：用REGEXP_MATCHES配合行源关联

将REGEXP_MATCHES作为行源，一次性获取所有捕获组的数组，再提取元素：

SELECT
  matches[1]::integer AS lower_bound,
  matches[2]::integer AS upper_bound
FROM REGEXP_MATCHES('$50M to $100M', '\\$(\\d+)M to \\$(\\d+)M') AS matches;

执行结果 #

两种方案都能得到预期输出：

内容的提问来源于stack exchange，提问作者arbind Sah