SQL> select ename,sal from emp;
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.
现在要求从ENAME列中删除元音字母,从SAL列中删除0。
SQL> select ename,
2 replace(translate(ename,'AEIOU','zzzzz'),'z','') as sub_ename, --先使用translate()把元音字母都转换为一个中间字母z,完了再使用replace取掉字母z
3 sal,
4 replace(sal,0,'') as sub_sal
5 from emp;
ENAME SUB_ENAME SAL SUB_SAL
---------- -------------------- ---------- --------------------
SMITH SMTH 800 8
ALLEN LLN 1600 16
WARD WRD 1250 125
JONES JNS 2975 2975
MARTIN MRTN 1250 125
BLAKE BLK 2850 285
CLARK CLRK 2450 245
SCOTT SCTT 3000 3
KING KNG 5000 5
TURNER TRNR 1500 15
ADAMS DMS 1100 11
ENAME SUB_ENAME SAL SUB_SAL
---------- -------------------- ---------- --------------------
JAMES JMS 950 95
FORD FRD 3000 3
MILLER MLLR 1300 13
14 rows selected.