SQL> with t as
2 (select regexp_substr(str, '[^\^]+', 1, level) as val
3 from (select 'A|RQ:0^B|SPF:3,0^C|RQ:3,1' as str from dual)
4 connect by level <= regexp_count(str, '\^') + 1),
5 s as
6 (select substr(val, 1, instr(val, '|', 1) - 1) as val_type,
7 substr(val,
8 instr(val, '|', 1) + 1,
9 instr(val, ':', 1) - instr(val, '|', 1) - 1) as val_option,
10 substr(val, instr(val, ':', 1) + 1) as val_res
11 from t),
12 a as (select val_type,val_option,val_res,regexp_count(val_res,',')+1 as cnt
13 from s)
14 select a.val_type,
15 a.val_option,
16 regexp_substr(a.val_res,'[^,]+',1,b.n) val_res
17 from a,(select level n
18 from dual
19 connect by level<= (select max(cnt) from a)) b
20 where a.cnt >= b.n
21 /
VAL_TYPE VAL_OPTION VAL_RES
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
A RQ 0
B SPF 3
B SPF 0
C RQ 3
C RQ 1