Oracle字段中使用 |或者其他符合分割后数据的取值

实例01：

CREATE TABLE T3 (
 LX VARCHAR2(10),
 DM VARCHAR2(20),
 QX VARCHAR2(3000)
 );
 insert into T3 values('CK09','mp702','about|accept|cancel|controlg|controlp');
 
select LX, DM, regexp_substr(QX, '[^|]+',1,level) new_item
from t3 connect by level <= regexp_count(qx,'|') + 1 
and prior rowid = rowid 
and prior dbms_random.value is not null
and regexp_substr(QX, '[^|]+',1,level) is not null

实例02：

create table a (
 cid number,
 num number);
insert into a (CID, NUM) values (101, 30);
insert into a (CID, NUM) values (102, 34);
insert into a (CID, NUM) values (185, 34);
insert into a (CID, NUM) values (230, 224);
insert into a (CID, NUM) values (345, 12);
create table b (
 id number,
 cid varchar2(100));
insert into b (ID, CID) values (1, '101|102|103|104');
insert into b (ID, CID) values (2, '201|223|432|235');
insert into b (ID, CID) values (3, '102|345|999|234|230');
with tmp as
 (select id, regexp_substr(cid, '[^|]+', 1, level) new_cid
    from b
  connect by level <= regexp_count(cid, '|') + 1
         and prior rowid = rowid
         and prior dbms_random.value is not null
         and regexp_substr(cid, '[^|]+', 1, level) is not null)
select * from a where a.cid not in (select new_cid from tmp);