Oracle character replacement function translate usage

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