SQL Reference - TRANSLATE

TRANSLATE(char, search_string, replacement_string)

char: This refers to the string value to be processed. The TRANSLATE Oracle function operates on this parameter by scanning it for occurrences of characters found in the search_string and replacing them with the corresponding characters in the replacement_string.
search_string: This is the string of characters that TRANSLATE will look for in the char parameter. Each character is independently searched, without recognizing any particular order within the string.
replacement_string: This string of characters is used by TRANSLATE for replacing characters in the char parameter that match the characters found in the search_string. The position of each character in the replacement_string corresponds to the position of the character to be replaced in the search_string.

Example

SELECT TRANSLATE('123-456-7890','0123456789','ABCDEFGHIJ') AS result
FROM dual;

Output

RESULT
----------
ABC-DEF-GHIJ

Explanation

In this example, the TRANSLATE function replaces each numeric character in the input string ‘123-456-7890’ with the corresponding character from the set ‘ABCDEFGHIJ’. Therefore, the function translates ‘1’ to ‘A’, ‘2’ to ‘B’, ‘3’ to ‘C’, ‘4’ to ‘D’, ‘5’ to ‘E’, ‘6’ to ‘F’, ‘7’ to ‘G’, ‘8’ to ‘H’, ‘9’ to ‘I’, and ‘0’ to ‘J’. The ’-’ is untouched since it’s not included in either set. The resulting string is ‘ABC-DEF-GHIJ’.

TRANSLATE(string text, from text, to text) RETURNS text

string text: The original text string that is to be manipulated.
from text: The characters in the original string that need to be replaced.
to text: The new characters that will replace the ‘from text’ characters in the original string.

Example

SELECT TRANSLATE('12345', '123', 'abc');

Output

abcdef

Explanation

The TRANSLATE function in PostgreSQL replaces each character in the string that matches a character in the from_string (second argument) with the corresponding character in the to_string (third argument). In the provided example, 1 is replaced with a, 2 with b, and 3 with c. As such, 12345 is transformed to abcdef.

TRANSLATE(input_string, characters, translations)

input_string: The string to be processed. This is the input which SQL Server will scan and perform translation operations upon.
characters: The characters that need to be replaced in the input_string. It is essentially a list of individual characters to be targeted for replacement in the input_string.
translations: The set of new characters that will replace the matched characters from the input_string. It is a list of individual characters that correspond positionally to the characters to be replaced listed in the characters parameter.

Example

SELECT TRANSLATE('12345', '123', 'abc') as TranslatedResult

Output

+---------------+
| TranslatedResult |
+---------------+
| abc45         |
+---------------+

Explanation

The TRANSLATE() function in this example replaces each character in the input string that matches a character in the second argument with the corresponding character in the third argument. ‘1’, ‘2’ and ‘3’ were replaced with ‘a’, ‘b’ and ‘c’ respectively, resulting in ‘abc45’.