Oracle字符拆分变多行记录

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

DROP TABLE TEST_SPLIT PURGE; CREATE TABLE TEST_SPLIT(DATA_ID NUMBER(10), NAME VARCHAR2(100)); INSERT INTO TEST_SPLIT VALUES(1,'语文'); INSERT INTO TEST_SPLIT VALUES(2,'英语;语文;数学'); INSERT INTO TEST_SPLIT VALUES(3,'政治;数学;地理;物理'); INSERT INTO TEST_SPLIT VALUES(4,'语文;美术;语文'); INSERT INTO TEST_SPLIT VALUES(5,'语文;数学'); COMMIT; SELECT * FROM TEST_SPLIT; DATA_ID    NAME 1          语文 2          英语;语文;数学 3          政治;数学;地理;物理 4          语文;美术;语文 5          语文;数学   SELECT T.DATA_ID, T.NAME,REGEXP_SUBSTR(T.NAME,'[^;]+',1,TB_LEVEL.LV) AS NAME_SPLIT   FROM (SELECT T.DATA_ID,T.NAME          FROM TEST_SPLIT T        )T,      (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL < 20) TB_LEVEL WHERE TB_LEVEL.LV <= REGEXP_COUNT(T.NAME, '\;') + 1 ORDER BY T.DATA_ID;