Oracle 正则 一行转多行

SELECT REGEXP_SUBSTR(bjdm||',', '[^,]+', 1, LEVEL, 'i') AS STR,bjdm FROM valueWeekInfo 
CONNECT BY LEVEL <= LENGTH(bjdm) - LENGTH(REGEXP_REPLACE(bjdm, ',', '')) + 1;  

可以将 bjdm 换成 '01,02,03,04' , valueWeekInfo 换成dual 查看结果

 

SELECT REGEXP_SUBSTR(zzjs, '[^,]+', 1, ROWNUM, 'i') str,zzjs FROM 
(select zzjs from bjpjxxb   group by zzjs )
CONNECT BY ROWNUM <= LENGTH(zzjs) - LENGTH(REGEXP_REPLACE(zzjs, ',', ''))+1

有时用RowNum会出现 一些问题,结合上面的LEVEL使用

 

 

 WITH test AS
   (SELECT 1 id1, 'aaa,bbb,ccc,ddd' id2
      FROM dual
    UNION ALL
    SELECT 2 id1, 'eee,fff,ggg,hhh,kkkk,ivwshjj' id2 FROM dual)
    
    SELECT id1,
         substr(t.ca,
                instr(t.ca, ',', 1, d.lv) + 1,
                instr(t.ca, ',', 1, d.lv + 1) -
                (instr(t.ca, ',', 1, d.lv) + 1)) AS d
    FROM (SELECT id1,
                 ',' || id2 || ',' AS ca,
                length(id2 || ',') - nvl(length(REPLACE(id2, ',')), 0) AS cnt
           FROM test) t,
         (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) d
   WHERE d.lv <= t.cnt
   ORDER BY id1
   
   
SELECT substr(t.classid,instr(t.classid, ',', 1, d.lv) + 1,
                instr(t.classid, ',', 1, d.lv + 1) -
                (instr(t.classid, ',', 1, d.lv) + 1))  str,classid,vid FROM 
(select vid,',' || classid || ',' classid
,length(classid || ',') - nvl(length(REPLACE(classid, ',')), 0) AS cnt from classvedio 
where createtime between '2015-09-01' and '2015-12-31' group by classid,vid) t,
(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) d
 WHERE d.lv <= t.cnt
 ORDER BY vid,str desc

 

SELECT REGEXP_SUBSTR(classid, '[^,]+', 1, lv, 'i') AS STR,classid,vid,lv FROM
(
select * from (
select vid,',' || classid || ',' classid
,length(classid || ',') - nvl(length(REPLACE(classid, ',')), 0) AS cnt
from classvedio) c,
(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= (select max(length(classid))  from classvedio)) d
WHERE d.lv <= c.cnt
 ORDER BY vid desc,lv asc
) 

最终完成版本

 

 /// <summary>
        /// 将某表的某列拆分成多行数据,返回的结构为
        /// str,拆分出来的每一个字段
        /// lv,拆分行数
        /// primarycols 
        /// key
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="primarycols">表主键列</param>
        /// <param name="key">要拆分的列</param>
       /// <param name="splitchar">分隔字符串</param>
       /// <returns></returns>
        public static string GetCommand(string tablename, string primarycols, string key,string splitchar)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(string.Format("SELECT REGEXP_SUBSTR({0}, '[^{2}]+', 1, lv, 'i') AS STR,{1},{0},lv FROM (", key, primarycols,splitchar));
            sb.AppendLine(string.Format("select * from (select {0},'{2}' || {1} || '{2}' {1}", primarycols, key,splitchar));
            sb.AppendLine(string.Format( ",length({0} || '{1}') - nvl(length(REPLACE({0}, '{1}')), 0) AS cnt",key,splitchar));
            sb.AppendLine(string.Format( "from {0}) c,",tablename));
            sb.AppendLine(string.Format("(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= (select max(length({0}))  from {1})) d", key, tablename));
            sb.AppendLine(string.Format( "WHERE d.lv <= c.cnt ORDER BY {0},lv asc",primarycols));
            sb.AppendLine(")");
            return sb.ToString();
        }