Oracle uses the TRANSLATE function to replace characters in the string

Source: Internet
Author: User
Tags translate function

Oracle uses the TRANSLATE function to replace one character in a string. How can I replace (12345, 1234, 56789) with '192*12345 '? First, replace: ChenZw> select replace ('(12345 )',',','*'),'(', ''''), ')', ''') from dual; REPLACE (RE ---------------- '2017*1234*56789 'has selected 1 line. The above code is obviously rude. Let's take a look at the official introduction of the Replace function: Purpose REPLACE returns char with every occurrence of search_string replaced with replacement_string. if replacement_string is omitted or null, then all occurrences ofsearch_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 data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, C LOB, 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. examples The following example replaces occurrences of J with BL: select replace ('Jack and JUE ', 'J', 'bl') "Changes" from dual; changes -------------- BLACK and BLUE method 2: Use the translate function: ChenZw> select translate ('(12 34,56789, 12345) ',' (,) ', ''' * ''') as str from dual; STR ---------------- '2017*1234*56789' selected 1 row. Using this function to solve this problem seems more comfortable, at least not as violent as replace. Let's take a look at the official introduction to the translate function: 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. the argument from_string can contain in more characters than to_string. in this case, the extra characters at the end offrom_string have no corresponding characters in to_string. if these extra characters appear in expr, then they are removed from the return value. if a character appears multiple times in from_string, then the to_string mapping corresponding to the first occurrence is used. 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. to remove all characters in from_string, concatenate another character to the beginning offrom_string and specify this character as the to_string. for example, TRANSLATE (expr, 'x0123456789 ', 'x') removes all digits from expr. 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. 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; TRANSLATE ('SQL * PLUSU--------------------SQL _ Plus_Users_Guide

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: 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.