Oracle 函数replace和translate的比较

今天看了SQL COOKBOOK中的一个例子，其中看到了两个函数Replace和Translate时产生了疑惑，感觉这两个函数的作用是一样的，书上面的例子也看的不是很明白，Google了一下，看了Oracle的官方解释，终于彻底明白了。官方的解释如下：

REPLACE

Description of the illustration replace.gif

Purpose

REPLACE returns char with every occurrence of search_string replaced with replacement_string . If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned.

Both search_string and replacement_string , as well as char , can be any of the datatypes CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , CLOB , or NCLOB . The string returned is in the same character set as char . The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.

REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

See Also:

TRANSLATE

 

Examples

The following example replaces occurrences of J with BL :

SELECT REPLACE('JACK and JUE','J','BL') "Changes"



FROM DUAL;



Changes



--------------



BLACK and BLUE

Translate

Description of the illustration translate.gif

Purpose

TRANSLATE
 returns expr
 with all occurrences of each character in from_string
 replaced by its corresponding character in to_string
. Characters in expr
 that are not in from_string
 are not replaced. If expr
 is a character string, then you must enclose it in single quotation marks. The argument from_string
 can contain more characters than to_string
. In this case, the extra characters at the end of from_string
 have no corresponding characters in to_string
. If these extra characters appear in char
, then they are removed from the return value.

You cannot use an empty string for to_string
 to remove all characters in from_string
 from the return value. Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null.

TRANSLATE
 provides functionality related to that provided by the REPLACE
 function. REPLACE
 lets you substitute a single string for another single string, as well as remove character strings. TRANSLATE
 lets you make several single-character, one-to-one substitutions in one operation.

This function does not support CLOB
 data directly. However, CLOB
s can be passed in as arguments through implicit data conversion.

 
 

  
  See Also:

 
 






"Datatype Comparison Rules"

 for more information and 
REPLACE

Examples

The following statement translates a book title into a string that could be used (for example) as a filename. The from_string
 contains four characters: a space, asterisk, slash, and apostrophe (with an extra apostrophe as the escape character). The to_string
 contains only three underscores. This leaves the fourth character in the from_string
 without a corresponding replacement, so apostrophes are dropped from the returned value.

SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;



TRANSLATE('SQL*PLUSU



--------------------



SQL_Plus_Users_Guide

select translate('liyan4h123ui','#liyanhui','#') from dual



结果：4123



select translate('asadad434323', '#0123456789','#') from dual



结果：asadad 




select TRANSLATE('kkaxksx', 'kx', '12') from dual



结果：11a21s2 



SQL CookBook 上這個例子分解



select translate('SMITH 20','0123456789','##########') from dual          將 

'SMITH 20'
 字符串中，所有數字替換為 ‘#’ 

結果是  
'SMITH ##'




select replace(translate('SMITH 20','0123456789','##########'),'#','') from dual   將  
 
'SMITH ##'
中 的'#'替換為 ''

結果是 'SMITH'