Oracle character replacement function translate usage

Source: Internet
Author: User
Tags translate function


Oracle character replacement function translate usage Oracle provides a character replacement function translate. Unlike the replace function, the translate function replaces character rather than string. The syntax is as follows: TRANSLATE (expr, from_string, to_string) simply replaces the characters in from_string with the characters in to_string for expr content. The example is as follows: SQL> select translate ('20160301', '20160301', 'abc') from dual; www.2cto.com TRANSLATE --------- 123abc789 SQL> select translate ('20160301', '20160301 ', 'AB') from dual; TRANSLAT -------- 123ab789 SQL> select translate ('20180101', '20180101', 'A') from dual; TRANSLAT ------- 123a789 SQL> select translate ('20180101' 9 ', '200', 'abcd') from dual; TRANSLATE --------- 123abc789 you can see that the translate function is replaced by character units one by one, as shown in the second and third examples, if the length of the third to_string parameter is less than that of the second from_string parameter, the excess parts after the from_string parameter are replaced with null values. The third and fourth examples show that if a character appears multiple times, the content of the first replacement prevails. A useful function is to count the number of times a character appears in a string: select length (translate ('expl', 'x' | 'expl ', from dual official document 10g Release 2 (10.2) The B14200-02 provides an example as follows: www.2cto.com Examples The following statement translates a book title into a string that cocould 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; ------------------ SQL _Plus_Users_Guide FROM semi-bottle Network

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.