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.