Oracle字符拆分变多行记录
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;
All for u