oracle 的两个替换函数 REPLACE、 TRANSLATE .

replace就是一般意义上的字符串替换

replace（c1，c2［，c3］）

在字符串c1中找到字符串c2，替换成c3.

若c3为空，则在c1中删除所有c2.

例：update articledoi set local_url=replace(

                (select local_url from articledoi where id=311) , ' :8888/pub ' , ':80 ' )

      where id=297;（单行替换）

translate只是一个字符替换对应的一个字符

TRANSLATE ( expr , from_string , to_string )

简单的说就是对expr内容，用to_string中的字符逐一替换from_string 中的字符，举例说明如下：

SQL> select translate('123456789','456','abc') from dual;

TRANSLATE
---------
123abc789

SQL> select translate('123456789','456','ab') from dual;

TRANSLAT
--------
123ab789

SQL> select translate('123456789','4564','a') from dual;

TRANSLAT
-------
123a789

SQL> select translate('123456789','4564','abcd') from dual;

TRANSLATE
---------
123abc789

可以看到translate函数是以字符为单位逐一替换的，从第二、三个例子可以看出，如果第三个参数to_string的长度小于第二个参数from_string，那么from_string的后边超出的部分都被替换为空值。从第三、四个例子可见，如果某一个字符多次出现，则以第一次替换的内容为准。

以此可以实现的一个有用的功能是统计一个字符串中某个字符出现的次数：

select length(translate('expl','x'||'expl','x')) from dual

 

简要比较：

      replace 字符串级别的代替

     如：SELECT REPLACE('accd','cd','ef') from dual; --> aefd

 

     translate 字符级别的代替

     如：select translate('acdd','cd','ef') from dual; -->aeff

 

分别详解

     replace：

     语法：REPLACE(char,search_string[,replacement_string])

     解释：replace中，每个search_string都被replacement_string所代替

        select replace('acdd','cd','ef') from dual; --> aefd

 

     如果replacement_string为空或为null，那么所有的search_string都被移除

     select replace('acdd','cd','') from dual; --> ad

 

     如果search_string 为null，那么就返回原来的char

     select replace('acdd','ef') from dual; -->acdd

     select replace('acdd','','') from dual; -->acdd(也是两者都为空的情况)

 

 

      translate:

      语法：TRANSLATE('char','from_string','to_string')

      解释：translate中，每个from_string中的字符被to_string中

        

        举例说明：

        

Sql代码 
SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual; TRANSLATE (   
--------------   
123456ghij   
 
SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual; TRANSL  
----------   
123456  
SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual; TRANSLATE (
--------------
123456ghij

SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual; TRANSL
----------
123456
 

   

Sql代码 
select TRANSLATE('kkaxksx', 'kx', '12') from dual   
 
结果：11a21s2 
select TRANSLATE('kkaxksx', 'kx', '12') from dual

结果：11a21s2 

translate中有“#”的特殊用法，以#开头的表示所有字符

translate的主要作用是提取,替换字符串,其作用有时候和replace差不多.具体看下面的例子

Sql代码 
select translate('liyan4h123ui','#liyanhui','#') from dual   
结果：4123    
select translate('liyan4h123ui','#liyanhui','#z') from dual;   
结果：z4123   
select translate('liyan4h123ui','#liyanhui','#zx') from dual;   
结果：zx4123x   
select translate('asadad434323', '#0123456789','#') from dual ;  
结果：asadad 
select translate('liyan4h123ui','#liyanhui','#') from dual
结果：4123
select translate('liyan4h123ui','#liyanhui','#z') from dual;
结果：z4123
select translate('liyan4h123ui','#liyanhui','#zx') from dual;
结果：zx4123x
select translate('asadad434323', '#0123456789','#') from dual ;
结果：asadad
 

利用TRANSLATE实现关键字的过滤
有时候需要对一些关键词语进行过滤，直接使用replace的话，可能由于这些关键词语比较多而要嵌套使用，语句也不好写，同时也浪费资源。这种情况其实可以使用TRANSLATE和replace组合使用就能完全达到目的了。

比如要将“深圳”、“北京”等作为关键词语，在显示内容是要将这些词语过滤掉不显示：

 

Sql代码 
--首先使用TRANSLATE将关键词语统一转换成一个特殊的字符串，比如这里的X   
 
SQL> select TRANSLATE('上海北京天津重庆广州深圳武汉','深圳北京','XXXX') from dual;   
TRANSLATE('上海北京天津重庆广?   
------------------------------   
上海XX天津重庆广州XX武汉   
--然后用replace将特殊的字符串替换掉。注意：不能用TRANSLATE直接将关键词语直接转换为''字符串   
 
SQL> select replace(TRANSLATE('上海北京天津重庆广州深圳武汉','深圳北京','XXXX'),'X') from dual;   
REPLACE(TRANSLATE('上海北京天?   
------------------------------   
上海天津重庆广州武汉   
 
SQL> --但是，用TRANSLATE是以一个字符为单位的，只要匹配到都会转换。比如不管“北”和“京”是否连接在一起都会做转换   
SQL> select TRANSLATE('上海京天津重庆北广州深圳武汉','深圳北京','XXXX') from dual;   
TRANSLATE('上海京天津重庆北广?   
------------------------------   
上海X天津重庆X广州XX武汉  
--首先使用TRANSLATE将关键词语统一转换成一个特殊的字符串，比如这里的X

SQL> select TRANSLATE('上海北京天津重庆广州深圳武汉','深圳北京','XXXX') from dual;
TRANSLATE('上海北京天津重庆广?
------------------------------
上海XX天津重庆广州XX武汉
--然后用replace将特殊的字符串替换掉。注意：不能用TRANSLATE直接将关键词语直接转换为''字符串

SQL> select replace(TRANSLATE('上海北京天津重庆广州深圳武汉','深圳北京','XXXX'),'X') from dual;
REPLACE(TRANSLATE('上海北京天?
------------------------------
上海天津重庆广州武汉

SQL> --但是，用TRANSLATE是以一个字符为单位的，只要匹配到都会转换。比如不管“北”和“京”是否连接在一起都会做转换
SQL> select TRANSLATE('上海京天津重庆北广州深圳武汉','深圳北京','XXXX') from dual;
TRANSLATE('上海京天津重庆北广?
------------------------------
上海X天津重庆X广州XX武汉

 

补充：TRANSLATE(string,from,to)转换的两个注意点——
1、转换源字串(from)在目的字串(to)中不存在对应，则转换后被截除
2、转换目的字串(to)不能为''，''在oracle中被视为空值，因此无法匹配而返回为空值

另外，一个汉字作为一个字符还是两个字符进行转换与字符集的设置相关。