Difference between replace and translate

REPLACE and TRANSLATE are character function in Oracle.

Replace function

Translate function

Both function looks similar but there are some subtle difference. We can say Translate is an extended version of Replace.

REPLACE TRANSLATE
Replaces entire string at a time Replaces character one-to-one basis
Returns string if no match found Returns null if no match found

Do you think any other difference. Please comment

8 responses to “Difference between replace and translate”

  1. Both Replace and Translate are single row functions.
    The Replace Function replaces single character with multiple characters.
    But in Translate Function replaces single character with single character only.

  2. Another Difference :
    replace function replaces a sequence of characters into another sequence of characters
    translate function also replaces a sequence of characters to other sequence of character,
    But the main difference is it replaces character by character.
    And it replaces a single character at a time.

  3. It’s Same as above… Both the same difference….

  4. Translate will return the same string if no search string is found. You are wrong at your second comparison point for translate.

    1. I will verify and let you know. Thanks for the comment.

  5. I will verify and let you know. Thanks for the comment.

  6. replace will return the same string if no search is found.translate will return null value if no search string is found

  7. Comments are more useful then content..

  8. replace is having two mandatory parameters and the third one is optional but for translate all 3 parameters should be given