Oracle SQL中如何用正则提取第n个（或最大）以|分隔的字段？

需求说明 #

给定以|分隔的字符串数据集：

Jack|Sparrow|17-09-16|DY7009|Address at some where|details 
Jack|Sparrow|17-09-16|DY7009|

需要实现：

• 提取到第5个|分隔字段对应的完整前缀内容（包含前5个字段及末尾的|）
• 若该行的分隔字段数不足5个，直接返回该行全部内容

原脚本问题分析 #

原脚本的逻辑存在两个关键问题：

1. REGEXP_instr(test,'[^|]+',1,5)定位的是第5个非|片段的起始位置，导致substr仅截取到第5个字段的首字符，而非完整字段内容
2. 当第5个字段不存在时（如第二行末尾仅为|，实际有效字段数为4），REGEXP_instr返回0，substr截取长度为0会直接返回null

正确实现方案 #

以下提供两种可靠的实现方式：

方式一：动态判断字段数后截取 #

WITH a AS (
  SELECT 'Jack|Sparrow|17-09-16|DY7009|Address at some where|details ' AS test FROM dual
  UNION ALL
  SELECT 'Jack|Sparrow|17-09-16|DY7009|' AS test FROM dual
)
SELECT
  CASE
    -- 统计非空字段数量，判断是否满足5个字段要求
    WHEN REGEXP_COUNT(test, '[^|]+') >= 5 THEN
      -- 匹配前5个字段及对应的分隔符，返回完整匹配内容
      REGEXP_SUBSTR(test, '^([^|]*\|){5}', 1, 1, NULL, 0)
    ELSE
      test
  END AS result
FROM a;

方式二：简洁正则直接匹配目标内容 #

WITH a AS (
  SELECT 'Jack|Sparrow|17-09-16|DY7009|Address at some where|details ' AS test FROM dual
  UNION ALL
  SELECT 'Jack|Sparrow|17-09-16|DY7009|' AS test FROM dual
)
SELECT
  -- 匹配前4个完整字段+第5个字段（含空字段）及末尾可选的|，返回捕获组内容
  REGEXP_SUBSTR(test, '^(([^|]*\|){4}[^|]*\|?).*', 1, 1, NULL, 1) AS result
FROM a;

验证结果 #

执行任一脚本，都会得到预期输出：

Jack|Sparrow|17-09-16|DY7009|Address at some where|
Jack|Sparrow|17-09-16|DY7009|

逻辑解释 #

• 方式一通过REGEXP_COUNT统计有效字段数，当满足条件时用正则匹配前5个字段的完整前缀；不足时直接返回原字符串，逻辑清晰易读
• 方式二用单个正则实现：([^|]*\|){4}匹配前4个字段加分隔符，[^|]*\|?匹配第5个字段（允许为空）及末尾的分隔符，最后通过捕获组提取目标内容，更为简洁

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