Difference between REPLACE AND TRANSLATE functions in oracle

REPLACE AND TRANSLATE:

REPLACE	TRANSLATE
Replace functions searches for a string and replaces with the given string	Replace functions searches for a character and its replaces in the occurrences of the character
Replace lets you substitute one string for another string	Translate function provides single character one to one substitution

REPLACE:

Example: 1

1. SELECT REPLACE ('abc-abd-abe-bcd-aaf', 'ab', '12') RESULT

     FROM DUAL;

Output:

Explanation:

‘ab’ is the replacement string, so where the ‘ab’ string is located its replaced into 12.

Example: 2

1. SELECT REPLACE ('abc-abd-abe-bcd-aaf', 'ab') RESULT

     FROM DUAL;

Output:

Explanation:

If we don’t give any replacement value replace function consider as NULL value.

Above example, ‘ab’ is the string replaced into null value.

Translate:

Example: 1

1. SELECT TRANSLATE ('abc-abd-abe-bcd-aaf','ab','12') RESULT

     FROM DUAL;

Output:

Explanation:

Here, ‘a’ replacement value is 1 and ‘b’ replacement value is 2 so its perform character like conversion.

Example: 2

1. SELECT TRANSLATE ('abc-abd-abe-bcd-aaf','ab','') RESULT

     FROM DUAL;

Output:

Explanation:

Here, the complete string replaced into NULL.

Example: 3

If we use without Third argument it shows error,

1. SELECT TRANSLATE ('abc-abd-abe-bcd-aaf','ab') RESULT

     FROM DUAL;

Report will be,

Share: