Sure. Here's the simple version, which gets you col1. This works a lot better if you use the splitting regexp substr from this question.
with t as (select 'PPID|1||123456789^^^^VV||PIZZA^KEVIN^^^^^L||98765432||' as str from dual)
select regexp_substr(t.str,'(.*?)(\||\^|$)', 1, level, null, 1) col1
from t
connect by level <= regexp_count(t.str, '(.*?)(\||\^|$)');
Adding your second column creates some significant complexity. There's probably a graceful way to do it by joining two hierarchical queries, but I can't do that well, so I just used some analytic functions.
with t as (select 'PPID|1||123456789^^^^VV||PIZZA^KEVIN^^^^^L||98765432||' as str from dual)
select col1,
'PID'
-- count pipes seen so far
|| trim(to_char(nvl(sum(case when sep = '|' then 1 else 0 end)
over (order by lev rows between unbounded preceding and 1 preceding)
,0)
,'00'))
-- count hats (within a partition defined by the number of pipes seen so far)
|| CASE when sep = '^' or lag(sep) over (order by lev) = '^' THEN
'-' || trim(to_char(row_number() over (partition by regexp_count(seen, '\|')
order by lev) - 1, '00'))
ELSE null end as col2
from (
select regexp_substr(t.str,'(.*?)(\||\^|$)', 1, level, null, 1) col1,
regexp_substr(t.str,'(.*?)(\||\^|$)', 1, level, null, 2) sep,
level as lev,
substr(t.str,1,regexp_instr(t.str,'(.*?)(\||\^|$)', 1, level, 0)) as seen
from t
connect by level <= regexp_count(t.str, '(.*?)(\||\^|$)')
) s
;
Output:
col1 col2
PPID PID00
1 PID01
PID02
123456789 PID03-01
PID03-02
PID03-03
PID03-04
VV PID03-05
PID04
PIZZA PID05-01
KEVIN PID05-02
PID05-03
PID05-04
PID05-05
PID05-06
L PID05-07
PID06
98765432 PID07
PID08
PID09
Let me know if you have any questions.
EDIT: Well, regexp_substr and hierarchical queries are both pretty slow. I rewrote it using MT0's recursive CTE no-regex answer on this question. It's still pretty sloppy, I'm sure it could be cleaned up.
WITH ex as (select 'PPID|1||123456789^^^^VV||PIZZA^KEVIN^^^^^L||98765432||' as str from dual),
t ( str, start_pos, end_pos ) AS
( SELECT str, 1, LEAST(INSTR(str, '|'),INSTR(str, '^')) FROM ex
UNION ALL
SELECT str,
end_pos + 1,
CASE WHEN INSTR(str, '|', end_pos + 1) > 0 and INSTR(str, '^', end_pos + 1) > 0 THEN
LEAST(INSTR(str, '|', end_pos + 1),INSTR(str, '^', end_pos + 1))
ELSE GREATEST(INSTR(str, '|', end_pos + 1),INSTR(str, '^', end_pos + 1)) END
FROM t
WHERE end_pos > 0
)
select col1,
'PID'
-- count pipes
|| trim(to_char(nvl(sum(case when rsep = '|' then 1 else 0 end)
over (order by start_pos rows between unbounded preceding and 1 preceding)
,0)
,'00'))
-- count hats
|| CASE when '^' in (lsep,rsep) THEN
'-' || trim(to_char(row_number() over (partition by (length(seen)-length(replace(seen, '|')))
order by start_pos), '00'))
ELSE null end
as col_seq
from (
SELECT str, start_pos, end_pos,
SUBSTR( str, start_pos, DECODE( end_pos, 0, LENGTH(str) + 1, end_pos ) - start_pos ) AS col1,
SUBSTR( str, start_pos-1, 1) as lsep, SUBSTR(str, DECODE( end_pos, 0, LENGTH(str) + 1, end_pos ), 1) as rsep,
SUBSTR( str, 1, DECODE( end_pos, 0, LENGTH(str) + 1, end_pos )-1 ) as seen
FROM t) s
order by start_pos;
