# 定序優先順序Collation Precedence

• 評估得出字元字串之運算式最終結果的定序。The collation of the final result of an expression that is evaluated to a character string.

• 輸入是字元字串而不傳回字元字串的區分定序運算子 (如 LIKE 和 IN) 所用的定序。The collation that is used by collation-sensitive operators that use character string inputs but do not return a character string, such as LIKE and IN.

## 定序規則Collation Rules

• 明確優先於隱含。Explicit takes precedence over implicit. 隱含優先於強制預設：Implicit takes precedence over Coercible-default:

明確 > 隱含 > 強制預設Explicit > Implicit > Coercible-default

• 組合兩個已指派了不同定序的明確運算式，會產生一則錯誤：Combining two Explicit expressions that have been assigned different collations generates an error:

明確 X + 明確 Y = 錯誤Explicit X + Explicit Y = Error

• 組合兩個含不同定序的隱含運算式會產生無定序的結果：Combining two Implicit expressions that have different collations yields a result of No-collation:

隱含 X + 隱含 Y = 無定序Implicit X + Implicit Y = No-collation

• 組合無定序運算式和任何標籤的運算式，除了明確定序 (請參閱下列規則) 之外，會產生含無定序標籤的結果：Combining an expression with No-collation with an expression of any label, except Explicit collation (see the following rule), yields a result that has the No-collation label:

無定序 + 任何項目 = 無定序No-collation + anything = No-collation

• 組合無定序運算式和明確定序運算式，會產生含明確標籤的運算式：Combining an expression with No-collation with an expression that has an Explicit collation, yields an expression with an Explicit label:

無定序 + 明確 X = 明確No-collation + Explicit X = Explicit

• 已是明確運算式的運算式，不能有多個 COLLATE 子句。You cannot have multiple COLLATE clauses on an expression that is already an explicit expression. 例如，下列 `WHERE` 子句無效，因為 `COLLATE` 子句的指定運算式已是明確運算式：For example, the following `WHERE` clause is not valid because a `COLLATE` clause is specified for an expression that is already an explicit expression:

`WHERE ColumnA = ( 'abc' COLLATE French_CI_AS) COLLATE French_CS_AS`

• 不允許進行 text 資料類型的字碼頁轉換。Code page conversions for text data types are not allowed. 若有不同的字碼頁，您便不能將 text 運算式的定序轉換成另外一個。You cannot cast a text expression from one collation to another if they have the different code pages. 當右文字運算元的定序，其字碼頁與左文字運算元不同時，指派運算子便不能指派值。The assignment operator cannot assign values when the collation of the right text operand has a different code page than the left text operand.

``````CREATE TABLE TestTab
(PrimaryKey int PRIMARY KEY,
CharCol char(10) COLLATE French_CI_AS
)

SELECT *
FROM TestTab
WHERE CharCol LIKE N'abc'
``````

### 定序規則的範例Examples of Collation Rules

``````USE tempdb;
GO

CREATE TABLE TestTab (
id int,
GreekCol nvarchar(10) collate greek_ci_as,
LatinCol nvarchar(10) collate latin1_general_cs_as
)
INSERT TestTab VALUES (1, N'A', N'a');
GO
``````

#### 定序衝突和錯誤Collation Conflict and Error

``````SELECT *
FROM TestTab
WHERE GreekCol = LatinCol;
``````

``````Msg 448, Level 16, State 9, Line 2
Cannot resolve collation conflict between 'Latin1_General_CS_AS' and 'Greek_CI_AS' in equal to operation.
``````

#### 明確標籤與隱含標籤Explicit Label vs. Implicit Label

``````SELECT *
FROM TestTab
WHERE GreekCol = LatinCol COLLATE greek_ci_as;
``````

``````id          GreekCol             LatinCol
----------- -------------------- --------------------
1 A                    a

(1 row affected)
``````

#### 無定序標籤No-Collation Labels

``````SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END)
FROM TestTab;
``````

``````Msg 451, Level 16, State 1, Line 1
Cannot resolve collation conflict for column 1 in SELECT statement.
``````
``````SELECT PATINDEX((CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END), 'a')
FROM TestTab;
``````

``````Msg 446, Level 16, State 9, Server LEIH2, Line 1
Cannot resolve collation conflict for patindex operation.
``````
``````SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END) COLLATE Latin1_General_CI_AS
FROM TestTab;
``````

``````--------------------
a

(1 row affected)
``````

## 區分定序和不區分定序Collation Sensitive and Collation Insensitive

### 函數和定序Functions and Collation

CAST、CONVERT，和 COLLATE 函式會針對 charvarchartext 資料類型區分定序。THE CAST, CONVERT, and COLLATE functions are collation sensitive for char, varchar, and text data types. 如果 CAST 和 CONVERT 函數的輸入和輸出是字元字串，輸出字串會採用輸入字串的定序標籤。If the input and output of the CAST and CONVERT functions are character strings, the output string has the collation label of the input string. 如果輸入不是字元字串，輸出字串就是強制預設，且會指派連接之目前資料庫的定序，或參考 CAST 或 CONVERT 的使用者自訂函數、預存程序或觸發程序所在的資料庫定序。If the input is not a character string, the output string is Coercible-default and assigned the collation of the current database for the connection, or the database that contains the user-defined function, stored procedure, or trigger in which the CAST or CONVERT is referenced.

CHARINDEXCHARINDEX REPLACEREPLACE
DIFFERENCEDIFFERENCE REVERSEREVERSE
ISNUMERICISNUMERIC RIGHTRIGHT
LEFTLEFT SOUNDEXSOUNDEX
LENLEN STUFFSTUFF
LOWERLOWER SUBSTRINGSUBSTRING
PATINDEXPATINDEX UPPERUPPER