oracle将以逗号隔开的字符串拆成多行

1、oracle将以逗号隔开的字符串拆成多行

create table USERS
(
    ID    VARCHAR2(10),
    NAME  VARCHAR2(20),
    HOBBY VARCHAR2(200)
);

INSERT INTO INDBADMIN.USERS (ID, NAME, HOBBY) VALUES ('1', '张三', '篮球,足球,足球');
INSERT INTO INDBADMIN.USERS (ID, NAME, HOBBY) VALUES ('2', '李四', '篮球,足球,乒乓');
INSERT INTO INDBADMIN.USERS (ID, NAME, HOBBY) VALUES ('3', '王五', '乒乓,羽毛球');
INSERT INTO INDBADMIN.USERS (ID, NAME, HOBBY) VALUES ('4', '赵六', null);
INSERT INTO INDBADMIN.USERS (ID, NAME, HOBBY) VALUES ('5', '孙七', '乒乓');

 

2、实现代码

SELECT distinct a.id, a.name, REGEXP_SUBSTR(a.hobby, '[^,]+', 1, LEVEL) AS hobby
FROM users a
CONNECT BY REGEXP_SUBSTR(a.hobby, '[^,]+', 1, LEVEL) IS NOT NULL
 AND PRIOR a.id = a.id
 AND PRIOR SYS_GUID() IS NOT NULL;

 

3、实现效果