TRANSLATE (Transact-SQL)
Applies to: SQL Server 2017 (14.x) and later Azure SQL Database Azure SQL Managed Instance
Returns the string provided as a first argument, after some characters specified in the second argument are translated into a destination set of characters, specified in the third argument.
TRANSLATE ( inputString, characters, translations )
The string expression to be searched. inputString can be any character data type (nvarchar, varchar, nchar, char).
A string expression containing characters that should be replaced. characters can be any character data type.
A string expression containing the replacement characters. translations must be the same data type and length as characters.
Returns a character expression of the same data type as inputString where characters from the second argument are replaced with the matching characters from third argument.
TRANSLATE
will return an error if characters and translations expressions have different lengths. TRANSLATE
will return NULL if any of the arguments are NULL.
The behavior of the TRANSLATE
function is similar to using multiple REPLACE functions. TRANSLATE
doesn't, however, replace any individual character in inputString
more than once. A single value in the characters
parameter can replace multiple characters in inputString
.
This is dissimilar to the behavior of multiple REPLACE
functions, as each function call would replace all relevant characters, even if they had been replaced by a previous nested REPLACE
function call.
TRANSLATE
is always SC collation aware. For more about supplementary collations, see Collation and Unicode support.
The following query replaces square and curly braces in the input string with parentheses:
SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');
Here's the result set.
2*(3+4)/(7-2)
In the following SELECT statement, you see a group of four nested calls to the REPLACE function. This group is equivalent to the one call made to the TRANSLATE function in the preceding SELECT:
SELECT
REPLACE
(
REPLACE
(
REPLACE
(
REPLACE
(
'2*[3+4]/{7-2}',
'[',
'('
),
']',
')'
),
'{',
'('
),
'}',
')'
);
GeoJSON is a format for encoding several geographic data structures. With the TRANSLATE
function, developers can easily convert GeoJSON points to WKT format and vice versa. The following query replaces square and curly braces in input with regular braces:
SELECT TRANSLATE('[137.4,72.3]' , '[,]', '( )') AS Point,
TRANSLATE('(137.4 72.3)' , '( )', '[,]') AS Coordinates;
Here's the result set.
Point | Coordinates |
---|---|
(137.4 72.3) | [137.4,72.3] |
SELECT TRANSLATE('abcdef','abc','bcd') AS Translated,
REPLACE(REPLACE(REPLACE('abcdef','a','b'),'b','c'),'c','d') AS Replaced;
Here's the result set.
Translated | Replaced |
---|---|
bcddef | ddddef |