SQL FUNCTION

Vikas Mishra

15y1.6kSQL

Hi friends,

what is the difference between replace and translate functions?

thanks...............

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes15y ago
Accepted 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 Mishra15y ago
Thanks vulpes
Satyapriya Nayak15y ago
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