regexp_substr SPILT

 select 

 regexp_substr(:P4_REVISED_ITEM, '[^:]+', 1, 1) "Item_Id", 

 regexp_substr(:P4_REVISED_ITEM, '[^:]+', 1, 3) "Description"

from dual;

--拆分字串, 例: 多個email依據分號;切分 ==>REGEXP_SUBSTR, CONNECT BY

SELECT NVL(REGEXP_SUBSTR('123@mail.com;333@mail;333333@mail', '[^;]+', 1, LEVEL, 'i'), 'NULL') AS STR 

FROM DUAL 

CONNECT BY LEVEL <= LENGTH('123@mail.com;333@mail;333333@mail') - LENGTH(REGEXP_REPLACE('123@mail.com;333@mail;333333@mail', ';', ''))+1;