Today, I read an example in SQL cookbook. I saw two functions replace and translate have doubts. I feel that these two functions have the same role, the examples in the book are not very clear. After Google's reading the official Oracle explanation, we finally fully understand it. The official explanation is as follows:
Replace
Description of the procedure replace.gif
Purpose
REPLACE
Returns
char
With every occurrence
search_string
Replaced
replacement_string
. If
replacement_string
Is omitted or null, then all occurrences
search_string
Are removed. If
search_string
Is null, then
char
Is returned. Both
search_string
And
replacement_string
, As well
char
, Can be any of the datatypes
CHAR
,
VARCHAR2
,
NCHAR
,
NVARCHAR2
,
CLOB
, Or
NCLOB
. The string returned is in the same character set
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
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
ExamplesThe following example replaces occurrences
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
Result: 4123
Select translate ('adad434323 ',' #0123456789 ',' # ') from dual
Result: asadad
Select translate ('kaxk', 'kx', '12') from dual
Result: 11a21s2