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, CLOBs 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'