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.
Syntax
TRANSLATE ( inputString, characters, translations )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
inputString
The string expression to be searched. inputString can be any character data type (nvarchar, varchar, nchar, char).
characters
A string expression containing characters that should be replaced. characters can be any character data type.
translations
A string expression containing the replacement characters. translations must be the same data type and length as characters.
Return types
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.
Remarks
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.
Examples
A. Replace square and curly braces with regular braces
The following query replaces square and curly braces in the input string with parentheses:
SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');
Here is the result set.
2*(3+4)/(7-2)
Equivalent calls to REPLACE
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}',
'[',
'('
),
']',
')'
),
'{',
'('
),
'}',
')'
);
B. Convert GeoJSON points into WKT
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 is the result set.
Point | Coordinates |
---|---|
(137.4 72.3) | [137.4,72.3] |
C. Use the TRANSLATE function
SELECT TRANSLATE('abcdef','abc','bcd') AS Translated,
REPLACE(REPLACE(REPLACE('abcdef','a','b'),'b','c'),'c','d') AS Replaced;
Here is the result set.
Translated | Replaced |
---|---|
bcddef | ddddef |