Monday, February 7, 2011

difference between translate and replace


Both Replace and Translate are single row functions in Oracle 9i.
The Replace Function replaces single character with multiple characters.
But in Translate Function replaces sinlge character with sinlge character only.
--
replace chages the record in database.
but translate won't change it the database content is not changed
E.g . 1
--------

For Example:
SQL> SELECT translate('So What' 'o' 'ay')
2 FROM dual;

TRANSLATE
-------
Sa What

SQL> SELECT REPLACE('So What' 'o' 'ay')
2 FROM dual;

REPLACE
--------
Say What

E.g . 2
--------

SQL> select replace('missisippi' 'is' '12') replace translate('missisippi' 'is' '12') translate from dual;
REPLACE TRANSLATE
---------- ----------
m12s12ippi m122121pp1

1 comment:

  1. brillant piece of information, I had come to know about your web-page from my friend hardkik, chennai,i have read atleast 9 posts of yours by now, and let me tell you, your webpage gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a million once again, Regards, Single Row Function in sql

    ReplyDelete