TRANSLATE
Description​
The TRANSLATE function is used for string replacement, converting characters in the source string according to mapping rules. It replaces characters in the source string that appear in the 'from' string with corresponding characters in the 'to' string.
Syntax​
TRANSLATE(<source>, <from>, <to>)
Parameters​
| Parameter | Description |
|---|---|
<source> | The source string to be converted. Type: VARCHAR |
<from> | The set of characters to be replaced. Type: VARCHAR |
<to> | The set of replacement characters. Type: VARCHAR |
Return Value​
Returns VARCHAR type, representing the transformed string.
Special cases:
- Returns NULL if any parameter is NULL
- If there are duplicate characters in the 'from' string, only the first occurrence is used
- If a source character is not in the 'from' string, it remains unchanged
- If a character's position in the 'from' string exceeds the length of the 'to' string, the corresponding source character will be deleted
- If both 'from' and 'to' are empty strings, returns the original string
Examples​
- Basic character replacement
SELECT translate('abcd', 'a', 'z');
+---------------------------+
| translate('abcd', 'a', 'z') |
+---------------------------+
| zbcd |
+---------------------------+
- Multiple replacements of the same character
SELECT translate('abcda', 'a', 'z');
+----------------------------+
| translate('abcda', 'a', 'z') |
+----------------------------+
| zbcdz |
+----------------------------+
- Special character replacement
SELECT translate('Palhoça', 'ç', 'c');
+--------------------------------+
| translate('Palhoça', 'ç', 'c') |
+--------------------------------+
| Palhoca |
+--------------------------------+
- Character deletion (empty 'to' string)
SELECT translate('abcd', 'a', '');
+----------------------------+
| translate('abcd', 'a', '') |
+----------------------------+
| bcd |
+----------------------------+
- Duplicate characters in 'from' string
SELECT translate('abcd', 'aac', 'zq');
+--------------------------------+
| translate('abcd', 'aac', 'zq') |
+--------------------------------+
| zbd |
+--------------------------------+
