ORACLE从字符串中删除不需要的字符

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.