SELECT ID,MONTH,WHOLE,
REGEXP_SUBSTR(tab.whole, '[^;]+', 1, d.level_) as part
FROM
(SELECT ID,MONTH,
WHOLE,
regexp_count(whole, '[^;]+') AS cnt --取待分割字段每行按照分隔符';'分割后的记录数,用于connect by
FROM test ) tab,
(SELECT rownum as level_--产生一个1-待拆分字段分割后最大记录数的序列
FROM (SELECT MAX(regexp_count(whole, '[^;]+')) AS max_len FROM test )
CONNECT BY level <= max_len) d
WHERE d.level_ <= tab.cnt --笛卡尔连接
order by id