Edit

TRANSLATE (Transact-SQL)

  • Article

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

See also