oracle 将多列,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;