需求:1
policyno(保单号)列:
0210130626000335001565/0211130626000332002226/0211130626000332002227/0211130626000332002227/0211130626000332002229/0211130626000332002210
这是由多个保单号组成:需要整理成多行:
policyno
0210130626000335001565
0211130626000332002226
0211130626000332002227
0211130626000332002227
0211130626000332002229
0211130626000332002210
select regexp_substr(a.policyno, '[^/| |;|£»|¡¢|£¬|\]+', 1, level) policyno1,
a.*
from ods_complain a
CONNECT BY PRIOR ROWID = ROWID
AND PRIOR DBMS_RANDOM.RANDOM IS NOT NULL
-- AND LEVEL <= REGEXP_COUNT(a.policyno, '[/| |;|£»|¡¢|£¬|\]') + 1
AND LEVEL <=(length(policyno) - length(replace(policyno, '/', '')) + 1)
如图显示:
需求2:
如图将author列,按'',''号拆分,然后统计author的个数:
结果: