Oracle查询优化-将字符和数字分离

直接上SQL
简单查询

select ename||sal as data from emp;

分离查询

select
  replace(translate(data,'1234567890','0000000000'),'0','') as ename,
  to_number(replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('
z',26,'z')),'z','')) as sal
  from ( select ename||sal as data from emp );

效果

DATA

SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
SCOTT3000
KING5000
TURNER1500
ADAMS1100

新