Oracle TRANSLATE() Function

The Oracle (PL/SQL) TRANSLATE() function returns a given string after replacing a sequence of characters in the string with another set of characters.

If string_to_replace is longer than replacement_string, occurrences of the extra characters in string_to_replace are removed from the given string. The function returns NULL if any of the arguments are NULL.

Syntax

TRANSLATE(string1, string_to_replace, replacement_string)

Parameters

`string1`	`Required.` Specify the string to replace a sequence of characters with another set of characters.
`string_to_replace`	`Required.` Specify the string that will be searched for in string1.
`replacement_string`	`Required.` Specify the replacement string. All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

Return Value

Returns the given string after replacing a sequence of characters in the string with another set of characters.

Example:

The example below shows the usage of TRANSLATE() function.

TRANSLATE('12345', '143', 'abc')
Result: 'a2cb5'

TRANSLATE('12345', '123', 'ABC')
Result: 'ABC45'

TRANSLATE('12345', '123', 'AB')
Result: 'AB45'

TRANSLATE('12345', '123', 'A')
Result: 'A45'

TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()')
Result: '2*(3+4)/(7-2)'

TRANSLATE('[137.4,72.3]' , '[,]', '( )')
Result: '(137.4 72.3)'

TRANSLATE('(137.4 72.3)' , '( )', '[,]')
Result: '[137.4,72.3]'

❮ Oracle Functions