強制SQL Server的查詢指令分辨英文字母大小寫

對一般人來說,你在安裝Microsoft SQL Server初始時,一選擇完預設定序(SQL_Latin1_General_CP1_CI_AS或是Chinese_Taiwan_Stroke_CI_AS)後,就再也沒有人去理會了,其實說真的,在一般的用途上,幾乎不會有怎麼樣的大問題。

今天會遇到的這個問題發生在我使用了自己撰寫的GUID壓縮演算法,在URL允許的情況下,利用字母大小寫的特性,硬是把總字串長度縮至22個字元。現在的問題就發生了,當這組壓縮過的GUID存到資料表中等待被SELECT時,這時候你的WHERE並不支援大小寫視別,也就是說,你的大小寫狀況一律會被忽略,更進一步說,壓縮過後的GUID碰撞機率大增!

所謂的GUID就是要降低碰撞率,哪有可能還讓SELECT時忽略大小寫來拉升碰撞機率,這可不行!因此我們的需求就出現了,也就是如何讓SQL SELECT中,去WHERE某個欄位時,我要強制那個欄位是需要分辨大小寫的。如果有一個資料表叫MEMBER_TABLE,然後裡面有一個叫cName的欄位,而剛好這個表裡面有兩個人的名字一樣,但是大小寫不一樣,分別是「John與john」,那我們要做到的是:

SELECT * FROM MEMBER_TABLE
WHERE cName = 'John'

//我們要做到只能出現John這一筆資料
//可是在這個指令裡,John與john這兩筆資料都被查出來

這一切的原因,就是因為沒有「Case Sensitive」在做祟,解決方法是:

SELECT * FROM MEMBER_TABLE
WHERE cName COLLATE Latin1_General_CS_AI = 'John'

//在這個指令裡,只會出現一筆John資料

SQL定序分類表

至於這CS、AI等等是什麼東西呢?解釋如下:

  1. Case Sensitivity (CS):強制視別大小寫
  2. Case Insensitive (CI):不強制視別大小寫
  3. Accent Sensitivity (AS):強制視別音腔,通常出現在歐洲語系
  4. Accent Insensitive (AI):不強制視別音腔
  5. Kana Sensitivity (KS):強制視別平假、片假名
  6. Kana Insensitive (KI):不強制視別平假、片假名
  7. Width Sensitivity (WS):強制視別中文的全型字與英文字,例如abc與abc是不一樣的
  8. Width Insensitive (WI):不強制視別中文的全型字與英文字

由上面的說明就可以知道,SQL的定序工程是如此的偉大且複雜,小弟我只有膜拜的份了!至於我們剛才設定的Latin1_General_CS_AI,那就是代表我要CS強制視別大小寫,但腔調部份我並不在意,所以選AS。因為我的壓縮GUID只有用到純英文字母與部份的特殊符號字,並沒有用到á、ó這種具備音腔的字母。

參考:Selecting a SQL Server Collation SqlServer SqlCommand Query WHERE CaseSensitive