translate
Description
Substitutes specified characters within a string. It works by taking a string (source
) as the input and replaces the from_string
characters in source
with to_string
.
This function is supported from v3.2.
Syntax
TRANSLATE(source, from_string, to_string)
Parameters
-
source
: supports theVARCHAR
type. The source string to be translated. If a character in thesource
is not found infrom_string
, it is simply included in the result string. -
from_string
: supports theVARCHAR
type. Each character infrom_string
is either replaced by its corresponding character in theto_string
, or if there is no corresponding character (i.e. ifto_string
has fewer characters than thefrom_string
, the character is excluded from the result string). See Examples 2 and 3. If a character appears multiple times infrom_string
, only its first occurrence is effective. See Example 5. -
to_string
: supports theVARCHAR
type. The string used to replace characters. If more characters are specified into_string
than in thefrom_string
argument, extra characters fromto_string
are ignored. See Example 4.
Return value
Returns a value of the VARCHAR
type.
Scenarios where the result is NULL
:
-
Any of the input parameters is
NULL
. -
The length of the result string after translation exceeds the maximum length (1048576) of
VARCHAR
.
Examples
-- Replace 'ab' in the source string with '12'.
mysql > select translate('abcabc', 'ab', '12') as test;
+--------+
| test |
+--------+
| 12c12c |
+--------+
-- Replace 'mf1' in the source string with 'to'. 'to' has less characters than 'mf1' and '1' is excluded from the result string.
mysql > select translate('s1m1a1rrfcks','mf1','to') as test;
+-----------+
| test |
+-----------+
| starrocks |
+-----------+
-- Replace 'ab' in the source string with '1'. '1' has less characters than 'ab' and 'b' is excluded from the result string.
mysql > select translate('abcabc', 'ab', '1') as test;
+------+
| test |
+------+
| 1c1c |
+------+
-- Replace 'ab' in the source string with '123'. '123' has more characters than 'ab' and '3' is ignored.
mysql > select translate('abcabc', 'ab', '123') as test;
+--------+
| test |
+--------+
| 12c12c |
+--------+
-- Replace 'aba' in the source string with '123'. 'a' appears twice and only the first occurrence of 'a' is replaced.
mysql > select translate('abcabc', 'aba', '123') as test;
+--------+
| test |
+--------+
| 12c12c |
+--------+
-- Use this function with repeat() and concat(). The result string exceeds the maximum length of VARCHAR and NULL is returned.
mysql > select translate(concat('bcde', repeat('a', 1024*1024-3)), 'a', 'z') as test;
+--------+
| test |
+--------+
| NULL |
+--------+
-- Use this function with length(), repeat(), and concat() to calculate the length of the result string.
mysql > select length(translate(concat('bcd', repeat('a', 1024*1024-3)), 'a', 'z')) as test;
+---------+
| test |
+---------+
| 1048576 |
+---------+