TRANSLATE (Transact-SQL)

APPLIES TO: yesSQL Server (starting with 2017) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

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) 

Arguments

inputString
Is the string expression to be searched. inputString can be any character data type (nvarchar, varchar, nchar, char).

characters
Is a string expression containing characters that should be replaced. characters can be any character data type.

translations
Is a string expression containing the replacment 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 arguements are NULL.

The behavior of the TRANSLATE function is similar to using multiple REPLACE functions. TRANSLATE does not, however, replace a character more than once. This is dissimilar to multiple REPLACE functions, as each use would replace all relevant characters.

TRANSLATE is always SC collation aware.

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, there is 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 a variety of 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;

The results are:

Translated Replaced
bcddef ddddef

See Also

CONCAT (Transact-SQL)
CONCAT_WS (Transact-SQL)
FORMATMESSAGE (Transact-SQL)
QUOTENAME (Transact-SQL)
REPLACE (Transact-SQL)
REVERSE (Transact-SQL)
STRING_AGG (Transact-SQL)
STRING_ESCAPE (Transact-SQL)
STUFF (Transact-SQL)
String Functions (Transact-SQL)