创建测试数据:
create table test1 (name varchar2(20), num varchar2(100));
insert into test1 values ('test','1,2,3');
insert into test1 values ('sugar','4,5,6');
查询语句:
SELECT name, REGEXP_SUBSTR(num, '[^,]+', 1, l) AS score
FROM test1, (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100)
WHERE L <= LENGTH(num) - LENGTH(REPLACE(num, ',')) + 1
ORDER BY 1, 2;
查询结果:
NAME SCORE
---------------------------------------- -----
sugar 4
sugar 5
sugar 6
test 1
test 2
test 3