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

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

8 thoughts on “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. Translate will return the same string if no search string is found. You are wrong at your second comparison point for translate.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s