一个选择是 regexp_substr() . 以下是一种考虑到分隔字符串中可能存在空槽的方法：

select
    regexp_substr(col, '([^|]*)(\||$)', 1, 1, null, 1) col1,
    regexp_substr(col, '([^|]*)(\||$)', 1, 2, null, 1) col2,
    regexp_substr(col, '([^|]*)(\||$)', 1, 3, null, 1) col3,
    regexp_substr(col, '([^|]*)(\||$)', 1, 4, null, 1) col4,
    regexp_substr(col, '([^|]*)(\||$)', 1, 5, null, 1) col5,
    regexp_substr(col, '([^|]*)(\||$)', 1, 6, null, 1) col6,
    regexp_substr(col, '([^|]*)(\||$)', 1, 7, null, 1) col7
from mytable

db小提琴演示：

with mytable as (select 'MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A' col from dual)
select
    regexp_substr(col, '([^|]*)(\||$)', 1, 1, '', 1) col1,
    regexp_substr(col, '([^|]*)(\||$)', 1, 2, '', 1) col2,
    regexp_substr(col, '([^|]*)(\||$)', 1, 3, '', 1) col3,
    regexp_substr(col, '([^|]*)(\||$)', 1, 4, '', 1) col4,
    regexp_substr(col, '([^|]*)(\||$)', 1, 5, '', 1) col5,
    regexp_substr(col, '([^|]*)(\||$)', 1, 6, '', 1) col6,
    regexp_substr(col, '([^|]*)(\||$)', 1, 7, '', 1) col7
from mytable

COL1  | COL2             | COL3 | COL4 | COL5       | COL6        | COL7
:---- | :--------------- | :--- | :--- | :--------- | :---------- | :---
MERGE | WorkRelationship | null | null | 2020/12/31 | RESIGNATION | Y

如果您要拆分的不仅仅是一个字符串，而且它包含数量可变的子字符串，这可能会有所帮助：

SQL> with test (col) as
  2    (select 'MERGE|WorkRelationship|||2020/12/31|RESIGNATION|Y||XYZ Limited|12345A' from dual
  3     union all
  4     select 'Little|Foot' from dual
  5    )
  6  select rtrim(regexp_substr(col, '[^|]*\|?', 1, column_value), '|') val
  7  from test cross join
  8    table(cast(multiset(select level from dual
  9                        connect by level <= regexp_count(col, '\|') + 1
 10                       ) as sys.odcinumberlist))
 11  order by col, column_value;

VAL
------------------------------
Little
Foot
MERGE
WorkRelationship

2020/12/31
RESIGNATION
Y

XYZ Limited
12345A

12 rows selected.

SQL>