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;

 

posted @ 2023-09-27 10:52  Ayumie  阅读(109)  评论(0编辑  收藏  举报