5 Replies Latest reply: Jul 18, 2012 1:06 AM by HuaMin Chen RSS

    difference between translate and replace function

    518916
      hi,
      what is the difference between translate and replace function
      can any one explain

      thanks in advance
        • 1. Re: difference between translate and replace function
          627777
          Translate translate the string byte by byte


          The a is replaced with an e, the h has no complement and is dropped.
          SELECT TRANSLATE('So What', 'ah', 'e')
          FROM dual;

          Replace only occurance of word with specified word if match found like o with ay

          SELECT REPLACE('So What', 'o', 'ay')
          FROM dual;
          UG
          • 2. Re: difference between translate and replace function
            Sven W.
            Read the documentation. Come back with specific examples and problems that you encounter.

            This is the xth question of that type that you ask. I will stop answering any more of you if you don't show any effort by yourself.
            • 3. Re: difference between translate and replace function
              950171
              1.     TRANSLATE (‘ABCED’,’AC’,’ac’)
              Result: aBcED
              Note: Here it changes character by character. If no replacement for a character then it will be replaced to Blank.

              2.     REPLACE (‘ABCED’,’AB’,’acvv’)
              Result: acvvCED
              Note: Here it changes one pattern by another.

              Edited by: 947168 on Jul 17, 2012 10:51 PM

              Edited by: 947168 on Jul 17, 2012 10:54 PM
              • 4. Re: difference between translate and replace function
                950171
                1.     TRANSLATE (‘ABCED’,’AC’,’ac’)
                Result: aBcED
                Note: Here it changes character by character. If no replacement for a character then it will be replaced to Blank.

                2.     REPLACE (‘ABCED’,’AB’,’acvv’)
                Result: acvvCED
                Note: Here it changes one pattern by another.
                • 5. Re: difference between translate and replace function
                  HuaMin Chen
                  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.

                  SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;

                  TRANSLATE('SQL*PLUSU
                  --------------------
                  SQL_Plus_Users_Guide

                  REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_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 datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, 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.

                  REPLACE provides functionality related to that provided by the 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

                  Examples

                  The following example replaces occurrences of J with BL:

                  SELECT REPLACE('JACK and JUE','J','BL') "Changes"
                  FROM DUAL;

                  Changes
                  --------------
                  BLACK and BLUE