隨著 SQL Server 7.0 引入 Unicode 資料型別、排序規則 等國際化功能,以及 SQL Server 2000 新增資料行層級排序規則,撰寫可在多語言環境間移植的 T-SQL 程式碼已成為可能。然而,許多開發者仍因對日期格式、字串比較、排序規則行為缺乏深入理解,而寫出在特定語系下失效的程式碼。本文將系統性探討日期處理、Unicode 採用、排序規則相依性三大核心議題,提供實用的可攜式寫法與最佳實務。
建議先閱讀 SQL Server 線上叢書主題「Writing International Transact-SQL Statements」(編寫國際性 Transact-SQL 陳述式)。
不同文化對「一週第一天」定義不同:
SET LANGUAGE us_english
SELECT DATEPART(dw, '20060606') -- 傳回 3 (星期二)
SET LANGUAGE british
SELECT DATEPART(dw, '20060606') -- 傳回 2 (星期二)
原因在於 @@DATEFIRST 預設值隨語系異動 (us_english=7/週日, british=1/週一)。
SET DATEFIRST 7 -- 強制週日為週起始,後續 DATEPART(dw) 行為一致
DECLARE @ndf_dw INT
SET LANGUAGE us_english
SELECT @ndf_dw = (@@DATEFIRST + DATEPART(dw, '20060606')) % 7 -- 結果 2
SET LANGUAGE british
SELECT @ndf_dw = (@@DATEFIRST + DATEPART(dw, '20060606')) % 7 -- 結果 2 (相同)
SELECT DATENAME(dw, '20060606')
-- us_english → 'Tuesday'
-- French → 'Mardi'
鐵則:比較/篩選月份或週日時,永遠使用數值日期部份 (MONTH(), DATEPART(dw, ...)),勿依賴字串名稱。
| 方法 | 範例 | 適用性 |
|---|---|---|
| RPC 參數化 (最推薦) | SqlCommand.Parameters.Add("@d", SqlDbType.DateTime).Value = dateVal | 所有語言、所有用戶端技術 (ADO.NET、ODBC、OLE DB) |
| ODBC 轉義子句 | {d '2006-06-06'}, {ts '2006-06-06 12:30:00'} | 任何支援 ODBC 語法的用戶端 |
| ISO 8601 無分隔格式 | '20060606' (yyyymmdd) | 所有語系皆正確解譯 |
| CONVERT 指定樣式 | CONVERT(datetime, '2006-06-06', 120) | 明確指定來源/目標格式,消除歧義 |
| 非 Unicode | Unicode (建議) | 備註 |
|---|---|---|
char | nchar | 固定長度 |
varchar | nvarchar | 變動長度 |
text | ntext | 大量文字 (SQL 2005+ 建議用 nvarchar(max)) |
CREATE TABLE #t1 (c1 NCHAR(1))
INSERT #t1 VALUES (N'Ω') -- 正確:希臘字母 Omega
INSERT #t1 VALUES ('Ω') -- 錯誤:轉為預設字碼頁,變成 'O'
SELECT * FROM #t1
-- 結果:Ω, O
規則:所有 Unicode 字串常數必須加上大寫 N 前置字元,否則 SQL Server 會先轉換為資料庫預設字碼頁再存入,導致字元遺失。
使用 SqlCommand 參數傳遞 Unicode 字串時,參數本身攜帶型別資訊,無需 N 前置字元,RPC 事件天然支援型別安全。
CREATE FUNCTION hexnum(@hexstr VARCHAR(10)) RETURNS BIGINT AS BEGIN
IF LEFT(@hexstr,2) IN ('0x','0X') SET @hexstr = SUBSTRING(@hexstr,3,10)
DECLARE @i INT, @res BIGINT, @l INT, @c CHAR
SELECT @i=1, @l=LEN(@hexstr), @res=0
IF @hexstr IS NULL OR @l=0 RETURN NULL
WHILE @i <= @l BEGIN
SET @c = UPPER(SUBSTRING(@hexstr,@i,1))
IF @c < '0' OR @c > 'F' RETURN NULL -- ❌ 排序規則相依!
-- ...
END
RETURN @res
END
在某些排序規則下,字元 'G' 可能排在 '0'~'F' 之間,導致無效字元通過檢查。測試 SELECT dbo.hexnum('0xG') 竟回傳 145 而非 NULL。
DECLARE @ascii0 INT = ASCII('0'), @asciiF INT = ASCII('F')
-- 在迴圈中:
IF NOT ASCII(@c) BETWEEN @ascii0 AND @asciiF RETURN NULL
完整修正版函數見文末程式碼區塊。
-- ❌ 錯誤:假設 LOWER/UPPER 在所有語系行為一致
SELECT au_id FROM authors WHERE LOWER(au_lname) = LOWER(@au_lname)
-- 土耳其語:'I' 小寫是 'ı' (無點),'İ' 大寫是 'i' (有點),破壞比較邏輯
CREATE TABLE authors (
au_lname VARCHAR(40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
-- CI = Case Insensitive, AS = Accent Sensitive
)
WHERE au_lname = @au_lname 即可不分大小寫比對核心觀念:在支援排序規則的語言中,勿嘗試用 T-SQL 手寫排序邏輯,直接宣告資料行排序規則才是正道。
CREATE FUNCTION dbo.hexnum(@hexstr VARCHAR(10)) RETURNS BIGINT AS BEGIN
IF LEFT(@hexstr,2) IN ('0x','0X') SET @hexstr = SUBSTRING(@hexstr,3,10)
DECLARE @i INT, @res BIGINT, @l INT, @c CHAR, @ascii0 INT, @asciiF INT
SELECT @i=1, @l=LEN(@hexstr), @res=0, @ascii0=ASCII('0'), @asciiF=ASCII('F')
IF @hexstr IS NULL OR @l=0 RETURN NULL
WHILE @i <= @l BEGIN
SET @c = UPPER(SUBSTRING(@hexstr,@i,1))
IF NOT ASCII(@c) BETWEEN @ascii0 AND @asciiF RETURN NULL
SET @res = @res + CAST(1.0 AS BIGINT) *
CASE WHEN ISNUMERIC(@c)=1 THEN CAST(@c AS INT) ELSE ASCII(@c)-55 END
* POWER(16, @l-@i)
SET @i = @i + 1
END
RETURN @res
END
nchar/nvarchar/ntextN 前置字元yyyymmdd 或參數化/RPC 傳遞DATENAME、DATEPART(dw) 用於邏輯判斷,改用規格化計算或 SET DATEFIRSTASCII()、COLLATE 或資料行層級排序規則CI 排序規則實現,而非 LOWER/UPPER 包裹掌握這些原則,配合 SQL Server 2000 完善的國際化工具集,即可撰寫真正跨語言、跨地區部署的強健 T-SQL 程式碼。