Tuesday, 26 May 2015

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 
---------- 
1BFGDGE


 select TRANSLATE(1255,2455,'BDEFGHI') AA from DUAL;

 OUTPUT:
---------------

  1BEE

No comments:

Post a Comment