SQL FUNCTION

Question

Hi friends, what is the difference between replace and translate functions? thanks...............

Vulpes · Answer

I imagine you're probably talking here about Oracle's version of SQL as SQL Server doesn't have a TRANSLATE function. The REPLACE function simply replaces all occurrences of the 'string to replace' with the 'replacement string'. The strings needn't be of the same length. In contrast, the TRANSLATE function works on a character by character basis. It takes each character in the 'string to replace' and replaces it with the corresponding character of the 'replacement string'. It follows that both strings need to be of the same length. An example may make this clearer. The first argument to both methods is the string within which to do the replacing, the second argument is the string to replace and the third argument is the replacement string: REPLACE('function', 'unc', 'rac') // gives 'fraction' TRANSLATE('function', 'unc', 'rac') // gives 'fractioa' because 'n' is replaced twice by 'a'

Vikas Mishra · Answer

Thanks vulpes

Satyapriya Nayak · Answer

Hi Vikas, REPLACE replaces every instance of a set of characters with another set of characters; that is, REPLACE works with entire words or patterns. TRANSLATE replaces single characters at a time, translating the nth character in the match set with the nth character in the replacement set. 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. SQL> select Translate('1tech23', '123', '456') from dual; Output ------- 4tech56 SQL> select replace('satyam','am','kam') from dual; Output ------- satykam Thanks