TRANSLATE (Transact-SQL)

**APPLIES TO:** ![yes](media/yes.png)SQL Server (starting with 2017) ![no](media/no.png)Azure SQL Database ![no](media/no.png)Azure SQL Data Warehouse ![no](media/no.png)Parallel 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.

Syntax

TRANSLATE ( inputString, characters, translations)

Arguments

inputString
Is an expression of any character type (nvarchar, varchar, nchar, char).

characters
Is a expression of any character type containing characters that should be replaced.

translations
Is a character expression that matches second argument by type and length.

Return Types

Returns a character expression of the same type as inputString where characters from the second argument are replaced with the matching characters from third argument.

Remarks

TRANSLATE function will return an error if characters and translations have different lengths. TRANSLATE function should return unchanged input if null vales are provided as characters or replacement arguments. The behavior of the TRANSLATE function should be identical to the REPLACE function.

The behavior of the TRANSLATE function is equivalent to using multiple REPLACE functions.

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)

[!NOTE] The TRANSLATE function in this example is equivalent to but much simplier than the following statement using REPLACE: 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;