SQL如何从包含多组数字的字符串中提取指定位置的数值

你需要的是从|分隔的Reference字段中提取指定位置的数值，可根据你使用的数据库选择对应实现方式：

通用正则方案（支持绝大多数主流数据库） #

直接匹配字符串中的第N个数字串，不依赖固定分隔符结构：

• 提取第1个数字（319）：REGEXP_SUBSTR(Reference, '\d+', 1, 1)
• 提取第2个数字（14163）：REGEXP_SUBSTR(Reference, '\d+', 1, 2)

多值查询示例：

SELECT
  REGEXP_SUBSTR(Reference, '\d+', 1, 2) AS SiblingId,
  REGEXP_SUBSTR(Reference, '\d+', 1, 1) AS ParentId
FROM table;

按分隔符拆分方案（结构固定时更稳定） #

MySQL #

使用SUBSTRING_INDEX嵌套实现：

SELECT
  -- 取第3段内容为14163
  SUBSTRING_INDEX(SUBSTRING_INDEX(Reference, '|', 3), '|', -1) AS SiblingId,
  -- 取第2段内容为319
  SUBSTRING_INDEX(SUBSTRING_INDEX(Reference, '|', 2), '|', -1) AS ParentId
FROM table;

PostgreSQL #

直接使用内置的SPLIT_PART函数：

SELECT
  SPLIT_PART(Reference, '|', 3) AS SiblingId,
  SPLIT_PART(Reference, '|', 2) AS ParentId
FROM table;

SQL Server 2016+ #

使用STRING_SPLIT带序号参数实现：

SELECT
  MAX(CASE WHEN ordinal = 2 THEN value END) AS ParentId,
  MAX(CASE WHEN ordinal = 3 THEN value END) AS SiblingId
FROM table
CROSS APPLY STRING_SPLIT(Reference, '|', 1)
GROUP BY 表主键, Reference;

Oracle #

正则拆分兼容空段写法：

SELECT
  REGEXP_SUBSTR(Reference, '([^|]*)(|$)', 1, 3, NULL, 1) AS SiblingId,
  REGEXP_SUBSTR(Reference, '([^|]*)(|$)', 1, 2, NULL, 1) AS ParentId
FROM table;

内容的提问来源于stack exchange，提问作者0brine