PostgreSQL中如何截取第三个下划线之前的子串？

你写的SQL逻辑不对，用REVERSE后找第一个下划线，实际定位的是原字符串最后一个下划线的位置，所以截取的是最后一个下划线之前的开头部分，自然得到22D。下面给你不同数据库的正确解法：

MySQL/MariaDB（最简单） #

用SUBSTRING_INDEX函数，直接按分隔符_取前3段：

SELECT SUBSTRING_INDEX('22D_XYZ_xy_100_xyz_123', '_', 3) AS result;

执行后直接返回22D_XYZ_xy，完全符合预期。

PostgreSQL/其他无SUBSTRING_INDEX的数据库 #

通过多次调用STRPOS定位第三个下划线的位置，再截取子串：

SELECT 
  SUBSTRING(
    '22D_XYZ_xy_100_xyz_123',
    1,
    -- 计算第三个下划线的位置，再减1得到截取终点
    STRPOS(
      SUBSTRING('22D_XYZ_xy_100_xyz_123', STRPOS('22D_XYZ_xy_100_xyz_123', '_') + STRPOS(SUBSTRING('22D_XYZ_xy_100_xyz_123', STRPOS('22D_XYZ_xy_100_xyz_123', '_') + 1), '_') + 1),
      '_'
    ) + STRPOS(SUBSTRING('22D_XYZ_xy_100_xyz_123', STRPOS('22D_XYZ_xy_100_xyz_123', '_') + 1), '_') + STRPOS('22D_XYZ_xy_100_xyz_123', '_') - 1
  ) AS result;

分步逻辑：

• 第一次STRPOS找第一个下划线的位置
• 第二次从第一个下划线后开始找第二个下划线的位置，加上第一个下划线的位置得到第二个下划线的总位置
• 第三次从第二个下划线后开始找第三个下划线的位置，加上前两个的位置得到第三个下划线的总位置
• 最后截取从开头到第三个下划线位置减1的部分，得到目标子串

通用分步写法（更易读） #

用CTE分步计算每个下划线的位置，可读性更强：

WITH str_data AS (
  SELECT '22D_XYZ_xy_100_xyz_123' AS original_str
),
pos1 AS (
  SELECT original_str, STRPOS(original_str, '_') AS p1 FROM str_data
),
pos2 AS (
  SELECT original_str, p1, STRPOS(SUBSTRING(original_str, p1 + 1), '_') + p1 AS p2 FROM pos1
),
pos3 AS (
  SELECT original_str, p2, STRPOS(SUBSTRING(original_str, p2 + 1), '_') + p2 AS p3 FROM pos2
)
SELECT SUBSTRING(original_str, 1, p3 - 1) AS result FROM pos3;

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