Oracle 中的replace和translate函数 以及关键字过滤

文章出处：
http://hi.baidu.com/oldwolfcn/blog/item/e164272a98c11f25d52af15b.html


正文：

Oracle 中的replace和translate函数 以及关键字过滤replace(str1,str2,str3)是把str1中的所有str2字符串替换成str3。

replace(str1,str2)是把str1 中的所有str2字符串去掉，也就是替换成''。


translate

一、语法：
TRANSLATE(string,from_str,to_str)
二、目的
返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string。TRANSLATE 是 REPLACE 所提供的功能的一个超集。如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中的额外字符将从 string 中被删除，因为它们没有相应的替换字符。to_str 不能为空。Oracle 将空字符串解释为 NULL，并且如果TRANSLATE 中的任何参数为NULL，那么结果也是 NULL。
三、允许使用的位置
过程性语句和SQL语句。
四、示例
SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual;
TRANSLATE (
--------------
123456ghij

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


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

translate的主要作用是提取,替换字符串,其作用有时候和replace差不多.具体看下面的例子
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组合使用就能完全达到目的了。

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

--首先使用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武汉

SQL>

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

另外，一个汉字作为一个字符还是两个字符进行转换与字符集的设置相关。
select TRANSLATE('kkaxksx', 'kx', '12') from dual
结果：11a21s2