Oracle将有特殊字符的长串字符转换成一个字段

在数据库中，有时会保存一段用特殊字符隔开的长串字段，比如F401:F402:F405:4011:4012:4021:4022:4041:4042:4043:4044:4045:4046:4047:4048:111
现在我需要将这一串字符转换成：
F401
F402
F405
4011
4012
4021
4022
4041
4042
4043
4044
4045
4046
4047
4048
111

我的SQL如下：

with aa as( 
SELECT CASE 
       WHEN DHGS = 1 THEN 1
       ELSE NVL(INSTR('F401:F402:F405:4011:4012:4021:4022:4041:4042:4043:4044:4045:4046:4047:4048:111', ':', 1, DHGS - 1), LENGTH('F401:F402:F405:4011:4012:4021:4022:4041:4042:4043:4044:4045:4046:4047:4048:111')) + 1
       END START_POI,      
       CASE
       WHEN DHGS > NVL(LENGTH(REGEXP_REPLACE('F401:F402:F405:4011:4012:4021:4022:4041:4042:4043:4044:4045:4046:4047:4048:111', '[^:]+', '')), 0) THEN LENGTH('F401:F402:F405:4011:4012:4021:4022:4041:4042:4043:4044:4045:4046:4047:4048:111')
       ELSE INSTR('F401:F402:F405:4011:4012:4021:4022:4041:4042:4043:4044:4045:4046:4047:4048:111', ':', 1, DHGS) - 1
       END END_POI
  FROM dual AA
  LEFT JOIN (SELECT ROWNUM DHGS FROM DUAL CONNECT BY ROWNUM < 200) BB 
  ON BB.DHGS <= NVL(LENGTH(REGEXP_REPLACE('F401:F402:F405:4011:4012:4021:4022:4041:4042:4043:4044:4045:4046:4047:4048:111', '[^:]+', '')),0) + 1)
 
 select tszf,aa.start_poi,aa.end_poi,substr(a.tszf,aa.start_poi,aa.end_poi-aa.start_poi+1) from 
(select 'F401:F402:F405:4011:4012:4021:4022:4041:4042:4043:4044:4045:4046:4047:4048:111'  tszf from dual ) a ,aa 

查询结果如下：

在with as 中，我利用了笛卡尔积将这个字符串中非‘:’的字符的开始位置与结束位置找出来，然后利用substr函数截取字段就可以了。