ORACLE将字符和数字数据分离

SQL> select ename||sal as data from emp;

DATA
--------------------------------------------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100

DATA
--------------------------------------------------
JAMES950
FORD3000
MILLER1300

14 rows selected.

想在要求把data分开为两列，字符部分一列，数字部分一列。

SQL> select
  2  replace(translate(data,'1234567890','0000000000'),'0','') as ename,
  3  to_number(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('
z',26,'z')),'z','')) as sal
  4  from
  5  (
  6  select ename||sal as data from emp
  7  );

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975
MARTIN                     1250
BLAKE                      2850
CLARK                      2450
SCOTT                      3000
KING                       5000
TURNER                     1500
ADAMS                      1100

ENAME                       SAL
-------------------- ----------
JAMES                       950
FORD                       3000
MILLER                     1300

14 rows selected.