Skip to content

translate-transact-sql.md

Latest commit

 

History

History
140 lines (105 loc) · 4.91 KB

translate-transact-sql.md

File metadata and controls

140 lines (105 loc) · 4.91 KB
title TRANSLATE (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 02/01/2019
ms.prod sql
ms.prod_service sql-database
ms.reviewer
ms.technology t-sql
ms.topic conceptual
f1_keywords
TRANSLATE
TRANSLATE_TSQL
helpviewer_keywords
TRANSLATE function
ms.assetid 0426fa90-ef6d-4d19-8207-02ee59f74aec
author MikeRayMSFT
ms.author mikeray
monikerRange >=sql-server-2017||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

TRANSLATE (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2017-asdb-xxxx-xxx-md]

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 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 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}', '[]{}', '()()');

[!INCLUDEssResult_md]

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;

[!INCLUDEssResult_md]

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)