[SQL]中文字排序和過濾的問題處理
遇到一個好友傳來一個問題，想要請我幫忙解釋為什麼資料在 SQL Server 內排序怎麼怪怪的，於是花了一點時間測試一下，他的案例是這樣的：
原本的狀況是這樣，資料也會按照所預定的排序去排序資料
-- 測試資料 DECLARE @TABLE TABLE ( NAME VARCHAR(10) ) INSERT @TABLE VALUES ( '舊' ), ( '勞' ), ( '9舊' ), ( '9勞' ) -- 測試指令 SELECT NAME FROM @TABLE ORDER BY NAME
但後來為了考量 UNICODE 的處理，因此把欄位的型態給換成的 NVARCHAR，但怎麼前面兩筆排列的方式和預期的不同呢 ?
-- 測試資料 DECLARE @TABLE TABLE ( NAME NVARCHAR(10) ) INSERT @TABLE VALUES ( N'舊' ), ( N'勞' ), ( N'9舊' ), ( N'9勞' ) -- 測試指令 SELECT NAME FROM @TABLE ORDER BY NAME
於是詢問一下他的測試環境中的定序，是採用在台灣很常被使用的 「Chinese_Taiwan_Stroke_BIN 」，以往會使用這樣的定序是因為把資料直接用 ASCII 排列，不用做一些轉換，因此會是比較快的一種，但資料為什麼改成 NVARCHAR 型態之後，會排列成這樣呢 ? 根據 MSDN 上的文件說明之中，有兩段很重要的敘述：
「如果是非 Unicode 資料類型，資料比較是依據 ANSI 字碼頁中所定義的字碼元素。 如果是 Unicode 資料類型，資料比較則是依據 Unicode 字碼指標。」
「舊版 SQL Server 中的二進位定序會以 WCHAR 比較第一個字元，然後使用逐一比較位元組的方式。 為了與舊版相容，現有的二進位定序語意不變。」
這樣看起來似乎還是很文言文， 我把範例改一下大家就比較容易看懂了
-- 範例資料 DECLARE @TABLE TABLE ( NAME NVARCHAR(10) ) INSERT @TABLE VALUES ( N'舊' ), ( N'勞' ), ( N'9舊' ), ( '9勞' ), ( N'張舊' ), ( '張勞' ) -- 範例指令 SELECT NAME, CAST(NAME as VARBINARY ) BINARY, UNICODE(SUBSTRING(NAME,1,1)) UNICODE1,UNICODE(SUBSTRING(NAME,2,1)) UNICODE2 FROM @TABLE ORDER BY NAME
從上面的表中看出，如果定序是 Chinese_Taiwan_Stroke_BIN 的模式下，第一個字會先用 UNICODE 比對，而「舊」和「勞」這兩個字剛好 ASCII CODE 和 UNICODE 的順序相反，因此如果用 ANSI 字碼表比較的時候「舊」會排在「勞」的前面；但如果用 UNICODE 來看的話，「勞」反而排在「舊」的前面；而在第一個字後面的，則會一個一個字都用 ANSI 字碼表來比對，因此會有這樣的狀況。
既然知道了原因，那要怎麼要來避免排序和過濾資料呢 ? 我個人是建議可以參考 MSDN 的作法，把資料庫的定序換成新版的二進位定序，也就是「Chinese_Taiwan_Stroke_BIN2 」
SELECT NAME, CAST(NAME as VARBINARY ) BINARY, UNICODE(SUBSTRING(NAME,1,1)) UNICODE1,UNICODE(SUBSTRING(NAME,2,1)) UNICODE2 FROM @TABLE ORDER BY NAME COLLATE Chinese_Taiwan_Stroke_BIN2
這樣雖然可以解決排序的問題，但如果我們要做判斷，要取出某個範圍的資料的時候，利用 Between 指令的時候，到底要怎麼判斷哪個值比較小要放前面，哪個要放後面呢 ?
-- 這兩個哪個才是正確的寫法呢 ? SELECT NAME FROM @TABLE WHERE NAME BETWEEN N'勞' and N'舊' SELECT NAME FROM @TABLE WHERE NAME BETWEEN N'舊' and N'勞'
如果您有這樣的困擾，或許您可以用個小偏方來試試看，把條件寫成以下的方式
-- 管他哪個大哪個小，反正就是這個區間內就對了 SELECT NAME FROM @TABLE WHERE (( NAME BETWEEN N'勞' and N'舊' ) OR ( NAME BETWEEN N'舊' and N'勞' ))
