Oracle regexp_substr

select regexp_substr('123|456|789', '[^|]+', 1, 2) from dual;