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

適用於:是SQL Server (從 2017 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2017) yesAzure SQL Database yesAzure 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 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 可以是任何字元資料類型。characters can be any character data type.

translations translations
為包含取代字元的字串運算式Is a string expression containing the replacement characters. translations 必須與 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.

RemarksRemarks

如果 characterstranslations 運算式的長度不同,則 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 不會多次取代字元。TRANSLATE does not, however, replace a character more than once. 這與多個 REPLACE 函式不同,因為每次使用時都會取代相關的所有字元。This is dissimilar to multiple REPLACE functions, as each use would replace all relevant characters.

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;

以下為結果集:Here is the result set.

Point 座標Coordinates
(137.4 72.3)(137.4 72.3) [137.4,72.3][137.4,72.3]

C.C. 使用 TRANSLATE 函式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
bcddefbcddef ddddefddddef

另請參閱See Also

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