Oracle之translate及replace函数用法总结

1、translate的用法

  作用：translate的主要作用是提取,替换字符串,其作用有时候和replace差不多。

  语法：TRANSLATE(char, from_string, to_string)

  对于char字符串参数中的每一个字符，如果该字符在 from_string参数中存在，则替换为对应的

   to_string参数中的字符，然后返回替换后的值。

注：

a、对于char字符串参数中的字符没有出现在from_string参数中则不用替换。

b、参数from_string中的字符个数多于to_string参数中的字符个数时，那么多出的字符

     就没有对应的字符来替换，这些字符就会从char参数中移除。

c、如果这个函数中任何一个参数为null，那么将返回null。

d、该函数不能直接作用于CLOB数据类型，可以通过将CLOB数据类型转换为字符串。

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

示例1：  

 select translate('abcdefga','abc','wo') 返回值 from dual; 返回值 wodefgw

示例2：

在实际的业务中，可以用来删除一些异常数据，比如表a中的一个字段t_no表示电话号码，

而电话号码本身应该是一个由数字组成的字符串，为了删除那些含有非数字的异常数据，

就用到了translate函数：

 delete from a where length(translate(trim(a.t_no),                                            '0123456789' || a.t_no,                                            '0123456789')) <> length(trim(a.t_no));

示例3：

select translate('abcd4e123fg','#abcdefg','#') from dual   --结果：4123     select translate('abcd4e123fg','#abcdefg','#z') from dual;   --结果：z4123    select translate('abcd4e123fg','#abcdefg','#zx') from dual;   --结果：zx4123     select translate('abcdefg434323', '#0123456789','#') from dual ;  --结果：abcdefg 

示例4：

数字保留，字符全部移除：SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789') "Translate example"FROM DUAL

示例5：

给指定字符打*号：SELECT TRANSLATE('浙江省', substr('浙江省',1,length('浙江省') - 1), rpad('*',length('浙江省'),'*'))sf  FROM DUAL;

2、replace的用法

语法：REPLACE(char, search_string,replacement_string)

作用：实现关键字的过滤。

用法：将char中的字符串search_string全部转换为字符串replacement_string。

示例：

举例：select REPLACE('abcdefg', 'ab' ,'g') 返回值 from dual;返回值gcdefgselect REPLACE('abcdefg', 'ab' ,'rt') 返回值 from dual;返回值rtcdefg