TRANSLATE (Transact-SQL)TRANSLATE (Transact-SQL)

04/16/2020

适用于：Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x) 及更高版本 Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database适用于：Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x) and later Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL Database

在第二个参数中指定的某些字符转换为第三个参数中指定的字符目标集后，返回作为第一个参数提供的字符串。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)

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法，请参阅早期版本文档。To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

inputString 是要搜索的字符串表达式。inputString Is the string expression to be searched. inputString 可以是任何字符数据类型（nvarchar、varchar、nchar、char）。inputString can be any character data type (nvarchar, varchar, nchar, char).

characters 是一个包含应替换字符的字符串表达式。characters Is a string expression containing characters that should be replaced. 字符可以是任何字符数据类型。characters can be any character data type.

translations 是一个包含替换字符的字符串表达式。translations Is a string expression containing the replacement characters. 转换必须与字符的数据类型和长度相同。translations must be the same data type and length as characters.

返回类型Return Types

返回与 inputString（第二个参数中的字符被替换为第三个参数中的匹配字符）具有相同数据类型的字符表达式。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 将返回错误。TRANSLATE will return an error if characters and translations expressions have different lengths. 如果任何参数为 NULL，TRANSLATE 将返回 NULL。TRANSLATE will return NULL if any of the arguments are NULL.

TRANSLATE 函数行为类似于使用多个 REPLACE 函数。The behavior of the TRANSLATE function is similar to using multiple REPLACE functions. 但是，TRANSLATE 不会在 inputString 中多次替换字符。TRANSLATE does not, however, replace any individual character in inputString more than once. characters 参数中的单个值可以替换 inputString 中的多个字符。A single value in the characters parameter, can replace multiple characters in inputString.

这不同于多个 REPLACE 函数的行为，因为每个函数调用都将替换所有相关字符，即使以前的嵌套 REPLACE 函数调用已替换它们。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 始终可以感知 SC 排序规则。TRANSLATE is always SC collation aware.

示例Examples

A.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)

与 REPLACE 等效的调用Equivalent calls to REPLACE

在以下 SELECT 语句中，有一组对 REPLACE 函数的四个嵌套调用。In the following SELECT statement, there is a group of four nested calls to the REPLACE function. 此组相当于上述 SELECT 中对 TRANSLATE 函数的一次调用：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.B. 将 GeoJSON 点转换为 WKTConvert GeoJSON points into WKT

GeoJSON 格式可用于对各种地理数据结构进行编码。GeoJSON is a format for encoding a variety of geographic data structures. 通过 TRANSLATE 函数，开发人员可以轻松地将 GeoJSON 点转换为 WKT 格式，反之亦然。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;