ORACLE 字符串定位、截取

SQL> select d.deptno, wm_concat(e.ename) nn
  2    from emp e, dept d
  3   where e.deptno = d.deptno
  4   group by d.deptno;


    DEPTNO NN
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD


SQL> select d.deptno,
  2         decode(instr(wm_concat(e.ename), ',', 1, 3),
  3                0,
  4                wm_concat(e.ename),
  5                substr(wm_concat(e.ename),
  6                       1,
  7                       instr(wm_concat(e.ename), ',', 1, 3) - 1)) nn
  8    from emp e, dept d
  9   where e.deptno = d.deptno
 10   group by d.deptno;


    DEPTNO NN
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS
        30 ALLEN,BLAKE,MARTIN