구분자 '|'의 갯수가 가변적이라면 connect by 를 사용해야 할것 같네요

substr, instr, union all 을 사용하세요..

전에 만들어 둔게 있는데요..

용도에 맞게 수정해서 쓰세요.. ^^;;

with tmp as (
    select 'aaa|bbbb|cccc' as str from dual
    union all select 'kk||zzz|aaaa|sdsd' from dual
)
select substr(str, idx_start+1, idx_end-idx_start-1 ) word
from (
   select
        str, idx_start,
        lead(idx_start) over(partition by str order by str, idx_start) idx_end
    from (
        select distinct str, instr(str, '|', c.no) idx_start
        from (select '|' || str || '|' str from tmp) a, copy_t c
        where c.no <= length(a.str)
    )
)
where idx_end is not null

with tmp as (
    select 'aa|bbb|cccc' str from dual
    union all select 'dddd|eeeee|ff|ggg' from dual
)
SELECT  REGEXP_SUBSTR(str,'[^|]+',1,LEVEL) str
FROM (select rownum SEQ, str from tmp)
CONNECT BY  CONNECT_BY_ROOT SEQ = SEQ
and LEVEL <= LENGTH(str) - LENGTH(REPLACE(str,'|')) + 1

with tmp as (
    select 'aa|bbb|cccc' str from dual union all

    select 'dddd|eeeee|ff|ggg' from dual
)
select regexp_substr(a.str,'[^\|]+',1,b.lv) str
from (select str,regexp_count(str,'\|') + 1 cnt from tmp) a
     inner join (select level lv from dual connect by level <= 100) b
     on b.lv <= a.cnt
order by a.str,b.lv