您可以使用

select (REGEXP_SUBSTR(replace('1:::9999', ' ',''), '([^: ]*)(:|$)', 1, 4, 'i', 1)) from dual;

这里，([^: ]*)(:|$)匹配

• ([^: ]*)-组1：除:和空格之外的任何零个或多个字符
• (:|$)-组2，:或字符串结尾。

对于此任务，您不需要（较慢的）正则表达式，请使用简单的substr/instr函数：

with input_(val) as (
  select '1:::9999' from dual
  union all
  select '1:2::' from dual
  union all
  select '1:2::3:5' from dual
)
, replaced as (
  select input_.*, replace(val, ' ', '') as val_replaced
  from input_
)
select
  val,
  substr(
    val_replaced,
    /*Locate the first occurrence of a colon and get a substring ...*/
    instr(val_replaced, ':', 1, 3) + 1,
    /*.. until the end, if the next colon is absent, or until the next colon*/
    nvl(nullif(instr(val_replaced, ':', 1, 4), 0), length(val_replaced) + 1) - instr(val_replaced, ':', 1, 3) - 1
  ) as col
from replaced

| 瓦尔|列|
| - -|- -|
| 1：：：9999|小行星9999|
| 1：2：|* 空值 *|
| 一比二比三比五|三个|
fiddle，但性能存在差异。