oracle 如何将带有,的一列分成多列

select regexp_substr('1,2,3' , '[^,]+' , 1 , ROWNUM) FROM dual CONNECT BY ROWNUM<=LENGTH('1,2,3')-LENGTH(regexp_replace('1,2,3' , ',' , '') )+1;

 

-------------------------------------------------------------------------------------

还有如果有多行，可以使用循环：

CREATE TABLE Data_CF(ID NUMBER,Colname VARCHAR2(2000));
------------
DECLARE
D_Col VARCHAR2(2000);
D_NUM NUMBER;
D_I NUMBER;
D_ID NUMBER;
BEGIN
FOR V IN(SELECT bsm,qlrmc FROM bdcdj.qlr WHERE qlrmc LIKE'%,%') LOOP
D_NUM:=LENGTH(v.qlrmc)-LENGTH(regexp_replace(v.qlrmc,',',''))+1;
D_I:=1;
D_ID:=v.bsm;
WHILE D_I<=D_NUM LOOP
SELECT regexp_substr(V.qlrmc,'[^,]+',1,D_I) INTO D_col FROM dual;
INSERT INTO Data_CF(ID,Colname)
values(D_ID,D_col);
D_I:=D_I+1;
END LOOP;
END LOOP;
END;
---
SELECT * FROM Data_CF ORDER BY ID;