oracle中删除某个字符串中,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.