SQL;一列转多行的使用 --正则表达式的使用

需求: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的个数:

结果: