replace就是通常意义的字符串替换函数,而translate是一种字符替换函数,它是把每一个查找字符串中的字符替换成替换字符串中的对应字符.
Description of the illustration replace.gif
Description of the illustration translate.gif
下面是oracle官方对这2个函数的说明:
REPLACE
Syntax
![Description of replace.gif follows Description of replace.gif follows](http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/img/replace.gif)
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
Syntax
![Description of translate.gif follows Description of translate.gif follows](http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/img/translate.gif)
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.
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 replace('kkaxksx', 'kx', '12') from dual
结果:kkaxksx
select TRANSLATE('kkaxksx', 'kx', '12') from dual
结果:11a21s2