您可以将整个列内容（包括最后的管道）作为一个组进行匹配，并使用匹配量词{m,n}捕获不超过5个组。然后使用regexp_substr提取匹配：

with a(test) as (
  select 'Jack|Sparrow|17-09-16|DY7009|Address at some where|details ' from dual
  union all select 'Jack|Sparrow|17-09-16|DY7009|' from dual
  union all select 'Jack|Sparrow|17-09-16' from dual
)
select
  test,
  REGEXP_substr(test,'([^|]+\|?){1,5}') as res
from a;

fiddle

尝试使用这样的查询，它应该可以解决您的问题：

with a as (
    select 'Jack|Sparrow|17-09-16|DY7009|Address at some where|details ' test from dual
    union all select 'Jack|Sparrow|17-09-16|DY7009|' test from dual
)
select 
  substr(
    test,
    1,
    decode(
      REGEXP_instr(test,'[^|]+', 1, 5, 1),
      0,
      REGEXP_instr(test,'[^|]+', 1, REGEXP_COUNT(test, '[^|]+'), 1),
      REGEXP_instr(test,'[^|]+', 1, 5, 1)
    )
  )
from a;

你可以使用查询here。

或者，在没有正则表达式的情况下，使用标准的substr + instr组合（在大型数据集上可能性能更好）。
样本数据（从astentx借用;谢谢！）：

SQL> with a (test) as (
  2    select 'Jack|Sparrow|17-09-16|DY7009|Address at some where|details ' from dual
  3    union all select 'Jack|Sparrow|17-09-16|DY7009|' from dual
  4    union all select 'Jack|Sparrow|17-09-16' from dual
  5  )

查询：

6  select test,
  7    substr(test, 1, case when instr(test, '|', 1, 5) = 0 then length(test)
  8                         else instr(test, '|', 1, 5) - 1
  9                    end
 10          ) as result
 11  from a;

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

SQL>