Comparison between replace and translate functions in Oracle

Source: Internet
Author: User

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 REPLACEReturns charWith every occurrence search_stringReplaced replacement_string. If replacement_stringIs omitted or null, then all occurrences search_stringAre removed. If search_stringIs null, then charIs returned. Both search_stringAnd 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 VARCHAR2If the first argument is not a lob and returns CLOBIf the first argument is a lob. REPLACEProvides functionality related to that provided by TRANSLATEFunction. TRANSLATEProvides Single-character, one-to-one substitution. REPLACELets you substitute one string for another as well as to remove character strings. See also:

Translate

 

ExamplesThe following example replaces occurrences JWith 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
Result: 4123
Select translate ('adad434323 ',' #0123456789 ',' # ') from dual
Result: asadad
Select translate ('kaxk', 'kx', '12') from dual
Result: 11a21s2

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.