【SQL】MSSQL COLLATE如何使用？

在 SQL Server 中，COLLATE 子句用於指定排序規則（collation）。排序規則決定了數據的比較和排序方式，特別是在處理不同語言和地區的字符時。這對於字符串比較、排序和存儲來說非常重要。

使用場景

1. 建立或修改資料庫、表或列：設置特定的排序規則。
2. 查詢中比較字符串：使用不同的排序規則進行字符串比較。

語法和範例

1. 建立資料庫時指定排序規則

CREATE DATABASE TestDB COLLATE Latin1_General_CI_AS;

在這個範例中，Latin1_General_CI_AS 是一種排序規則，表示不區分大小寫（CI）和區分重音（AS）。

2. 建立資料表時指定列的排序規則

CREATE TABLE TestTable (
ID INT,
Name NVARCHAR(100) COLLATE Latin1_General_CS_AS
);

在這裡，Name 列使用 Latin1_General_CS_AS 排序規則，表示區分大小寫（CS）和區分重音（AS）。

3. 修改列的排序規則

ALTER TABLE TestTable
ALTER COLUMN Name NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS;

這個範例將 Name 列的排序規則更改為 SQL_Latin1_General_CP1_CI_AS，表示不區分大小寫（CI）和區分重音（AS）。

4. 查詢中使用 COLLATE

有時，你需要在查詢中臨時使用不同的排序規則進行比較。

SELECT *
FROM TestTable
WHERE Name COLLATE Latin1_General_CS_AS = ‘test’;

在這裡，Name 列在比較時使用 Latin1_General_CS_AS 排序規則。

常見的排序規則

• CI：Case Insensitive，不區分大小寫。
• CS：Case Sensitive，區分大小寫。
• AI：Accent Insensitive，不區分重音。
• AS：Accent Sensitive，區分重音。

查詢資料庫和列的排序規則

1. 查詢資料庫的排序規則

SELECT DATABASEPROPERTYEX(‘TestDB’, ‘Collation’) AS DatabaseCollation;

2.查詢列的排序規則

SELECT COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘TestTable’;

總結

COLLATE 子句在 SQL Server 中是一個強大而靈活的工具，允許開發者控制字符串的比較和排序方式。這在多語言環境或需要特殊排序規則的應用中尤為重要。通過指定適當的排序規則，你可以確保數據以預期的方式進行處理。