What is difference between replace and translate functions

 

Replace function searches for a string and replaces with the given string.
Translate function searches for a character and it replaces in occurrence of the character.
REPLACE

The Replace function replaces one value in a string with another.
For example, you can replace each occurrence of a letter with matching number.

REPLACE (char,search_string,replace_string)

If value for replace_string is not specify, the search_string value, when found, is removed.

Possible input can be any character data types, like CHAR, VARCHAR2,NCHAR,CLOB.

     SELECT REPLACE('COMPUTER','OM','AB')
        FROM dual;

Output
-----------
CABPUTER

TRANSLATE

Translate does an orderly character-by-character substitution in a string.

TRANSLATE (string,if,then)

    SELECT TRANSLATE(1256364,2345678,'BDEFGHI')
        FROM dual;
Output
----------
BFGDGE

5 comments:

  1. UnknownDecember 24, 2013 at 8:36 AM

    The correct output is

    SELECT TRANSLATE(1256364,2345678,'BDEFGHI')
    FROM dual;
    Output
    ----------
    1BFGDGE

    ReplyDelete
  2. UnknownFebruary 24, 2014 at 9:24 AM

    can we have more example of translate ?

    ReplyDelete
  3. UnknownMay 8, 2016 at 7:28 PM

    SELECT
    'GANESH' STRING,
    TRANSLATE('GANESH','GN','RM')TRANSLATE
    FROM DUAL;

    O/P:
    STRING TRANSL
    ------ ------
    GANESH RAMESH

    EXPLANATION:

    TRANSLATE('GANESH','GN','RM');

    G=R,N=M

    WHERE IS THE G IS THERE TRANSLATE TO R
    WHERE IS THE N IS THERE TRANSLATE TO M GIVEN PATTERN.

    ReplyDelete

Subscribe to: Post Comments (Atom)