select distinct id, regexp_substr(uname, '[^,]+', 1, level) uname from a connect by level <= length(regexp_replace(uname, '[^,]+')) - 1 order by id;
如果你的oracle版本是10g以上, 可以用这个:SELECT REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, rn) b FROM dual, (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <= 50) WHERE REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, rn) IS NOT NULL
去掉connect by level <= length(regexp_replace(uname, '[^,]+')) -1中的减一
connect by level <= length(regexp_replace(uname, '[^,]+')) - 1
order by id;
可以用这个:SELECT REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, rn) b FROM dual,
(SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <= 50)
WHERE REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, rn) IS NOT NULL
去掉connect by level <= length(regexp_replace(uname, '[^,]+')) -1中的减一