translate replace区别

最新推荐文章于 2023-03-22 14:59:56 发布

iteye_7135

最新推荐文章于 2023-03-22 14:59:56 发布

阅读量118

点赞数

分类专栏： oracle sql

本文链接：https://blog.csdn.net/iteye_7135/article/details/81900317

版权

oracle sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据：

fuyou@ORCL> select ename||sal from emp;

ENAME||SAL
------------------------------------------------------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK3675
SCOTT3000
KING7500
TURNER1500
ADAMS1100
JAMES950
FORD3000
MILLER1300

已选择14行。

利于translate将字母全部改成z

fuyou@ORCL>  select data ,translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z'))
  2    from
  3    ( select ename||sal  as data from emp);

DATA                                                         TRANSLATE(LOWER(DATA),'ABCDEFGHIJKLMNOPQRSTUVWXYZ',RPAD('Z',26,'Z'))
------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------
SMITH800                                                     zzzzz800
ALLEN1600                                                    zzzzz1600
WARD1250                                                     zzzz1250
JONES2975                                                    zzzzz2975
MARTIN1250                                                   zzzzzz1250
BLAKE2850                                                    zzzzz2850
CLARK3675                                                    zzzzz3675
SCOTT3000                                                    zzzzz3000
KING7500                                                     zzzz7500
TURNER1500                                                   zzzzzz1500
ADAMS1100                                                    zzzzz1100
JAMES950                                                     zzzzz950
FORD3000                                                     zzzz3000
MILLER1300                                                   zzzzzz1300

利于replace删除z

fuyou@ORCL> select data ,replace(translate(lower(data),'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')),'z')
  2  from
  3  ( select ename||sal  as data from emp);

DATA                                                         REPLACE(TRANSLATE(LOWER(DATA),'ABCDEFGHIJKLMNOPQRSTUVWXYZ',RPAD('Z',26,'Z')),'Z')
------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------
SMITH800                                                     800
ALLEN1600                                                    1600
WARD1250                                                     1250
JONES2975                                                    2975
MARTIN1250                                                   1250
BLAKE2850                                                    2850
CLARK3675                                                    3675
SCOTT3000                                                    3000
KING7500                                                     7500
TURNER1500                                                   1500
ADAMS1100                                                    1100
JAMES950                                                     950
FORD3000                                                     3000
MILLER1300                                                   1300

已选择14行。