TRANSLATE

This function replaces characters in a string expr using corresponding entries from source_alphabet and target_alphabet.

Syntax

TRANSLATE(expr, source_alphabet, target_alphabet)

Arguments

expr: String. If characters in expr are not in source_alphabet, they are included in the return value unchanged.

---

source_alphabet: A string containing all characters to be changed in expr. Each character is either translated to the corresponding character in target_alphabet or deleted if target_alphabet has no replacement for it, e.g., if the length of target_alphabet is shorter than the length of source_alphabet (see "result3" in the "Examples").

---

target_alphabet: A string containing all characters used to replace characters from source_alphabet. If target_alphabet is longer than source_alphabet, an error is returned.

Usage Notes

• If a character appears multiple times in source_alphabet, the target_alphabet mapping corresponding to the first occurrence is used.
• It is not allowed to use an empty string for target_alphabet to remove all characters in source_alphabet from the return value, as the empty string is interpreted as NULL. In this case, the return value is also NULL.
• To remove all characters in source_alphabet, you can add an additional character at the beginning of source_alphabet and specify this character as a single character in target_alphabet. Example: TRANSLATE(expr, 'd0123456789', 'd') removes all digits from expr.

Examples

select translate('reña','ñ','n') as result1, 
       translate('brot','rtob','ereb') as result2, 
       translate('abcd','bdca','acb') as result3, 
       translate('abcd1234','x0123456789','x') as result4;

+---------+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 | RESULT4 |
+---------+---------+---------+---------+
| rena    | beer    | abc     | abcd    |
+---------+---------+---------+---------+