do something: regexp

2022年2月15日星期二

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;