TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

偷个懒，直接拷贝了Oracle的在线文档，嘿嘿!

REPLACE

Syntax

replace::=

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 of VARCHAR2 datatype and is in the same character set as char.

This function 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.

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
Syntax
translate::=
 
Text description of translate

Purpose
TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in char that are not in from_string are not replaced. 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 interprets the empty string as null, and if this function has a null argument, then it returns null.
Note: 
This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. Please refer to "Datatype Comparison Rules" for more information.
Examples
The following statement translates a license number. All letters 'ABC...Z' are translated to 'X' and all digits '012 . . . 9' are translated to '9':
SELECT TRANSLATE('2KRW229',
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') "License"
     FROM DUAL;
 
License
--------
9XXX999 

The following statement returns a license number with the characters removed and the digits remaining:
SELECT TRANSLATE('2KRW229',
   '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789') 
   "Translate example"
     FROM DUAL;
 
Translate example
-----------------
2229