请问一下这样的需求可以实现吗？

请问一下这样的需求可以实现吗？
http://www.itpub.net/thread-1511467-1-1.html

 

bell6248：

对于表A字段state的拆分可以使用如下的方法， 接着就可以进行进一步处理了！

select t1.*,
       substr(',' || t1.state || ',',
              instr(',' || t1.state || ',', ',', 1, t2.rn) + 1,
              instr(',' || t1.state || ',', ',', 1, t2.rn + 1) - instr(',' || t1.state || ',', ',', 1, t2.rn) - 1) c
from
tb_a t1,
(select rownum rn from dual connect by rownum <= (select max(length(state) - length(replace(state, ',', ''))) from tb_a) + 1) t2
where length(t1.state) - length(replace(t1.state, ',', '')) + 1 >= t2.rn
order by t1.historyid, t2.rn;

dingjun123：

你这个可以直接将两边的加逗号，然后instr >0就可以了，如果要拆分也可以
SQL>    SELECT * FROM test;

         A B
---------- ------------------------------
         1 1,2,3
         2 8,4,5,7

SQL>
SQL>  SELECT a, SUBSTR (b,
  2             INSTR (b, ',', 1, rn  ) + 1,
  3             INSTR (b, ',', 1, rn+1)
  4             - INSTR (b, ',', 1, rn) -1 ) b
  5    FROM (SELECT a, ','||b||',' AS b
  6                FROM test) test,
  7    (SELECT LEVEL rn FROM DUAL
  8                       CONNECT BY LEVEL<=(SELECT MAX(length(trim(translate(b,replace(b,','),' '))))+1 FROM test))
  9  WHERE SUBSTR (b,
10             INSTR (b, ',', 1, rn  ) + 1,
11             INSTR (b, ',', 1, rn+1)
12             - INSTR (b, ',', 1, rn) -1 ) IS NOT NULL
13  ORDER BY b,rn;

         A B
---------- ----------------------------------------------------------------
         1 1
         1 2
         1 3
         2 4
         2 5
         2 7
         2 8

7 rows selected

SQL>
SQL>  SELECT a,REGEXP_SUBSTR(b,'[^,]+',1,rn) b
  2    FROM test,(SELECT LEVEL rn FROM DUAL
  3                       CONNECT BY LEVEL<=(SELECT MAX(length(trim(translate(b,replace(b,','),' '))))+1 FROM test))
  4  WHERE REGEXP_SUBSTR(b,'[^,]+',1,rn) IS NOT NULL
  5  ORDER BY b,rn;

         A B
---------- ------------------------------------------------------------
         1 1
         1 2
         1 3
         2 4
         2 5
         2 7
         2 8

7 rows selected