システムの制約上、全テーブルのデータ型が文字列のすべての列の照合順序がバイナリ（Japanese_BIN）になっている場合があります。バイナリでは大文字と小文字、全角と半角などは、すべて別の文字として照合されます。
コード値などはバイナリで完全一致比較をした方がいい場合もありますが、名前や備考などの文字列では、完全一致よりも曖昧一致した方がユーザーにとって便利な場合が多々あります。
そこで今回は、SQL Serverに作成されているテーブルの列がバイナリ比較になっている場合に、大文字と小文字や全角と半角を区別せずに検索条件を照合する方法を紹介します。
照合順序とは
照合順序は、文字の大小関係を比較する際の基準となる規則です。
照合順序は、文字の並べ替えや検索条件の照合などに利用されます。
照合順序は、インデックスの作成時、ORDER BYでのソート時、GROUP BYでのグループ化時、WHEREでの対象の抽出など、様々な場面で使われています。
SQL Serverでは、照合順序のことをCOLLATE（コレート）、またはCOLLATION（コレーション）と呼んでいます。
照合順序には「大文字と小文字の区別」「アクセント（濁音、半濁音の有無）の区別」「ひらがなとカタカナの区別」「半角と全角の区別」があります。
SQL Server 2012以降では、辞書順に並べる際に補助文字を認識するかどうかを区別する指定もありますが、今回の記事では割愛します。
照合順序には「SQL 照合順序」と「Windows 照合順序」があります。
「SQL 照合順序」はSQL Server 6.5以前のバージョンとの互換性のみを目的としている照合順序で、Unicodeデータ型をサポートしていませんので、通常は「Windows 照合順序」を使用します。
本記事内での照合順序は、すべて「Windows 照合順序」のことになります。
Windows 照合順序には以下の5種類があります。
| No | 照合順序 | バージョン |
|---|---|---|
| 1 | Japanese_Unicode | 7.0～ |
| 2 | Japanese | 2000～ |
| 3 | Japanese_90 | 2005～ |
| 4 | Japanese_XJIS_100 | 2008～ |
| 5 | Japanese_Bushu_Kakusu_100 | 2008～ |
照合順序には、上記のキーワードの後にどのような文字を区別するかを表すパラメータが付きます。
2のJapaneseを例にすると「Japanese_XX」となります。XXの部分がパラメーターです。照合順序とパラメーターはアンダースコア「_」でつなげます。
パラメーターと照合順序の指定
ここでは、照合順序に付加するパラメーターと照合順序の指定について記載します。
区別の種類
大文字と小文字の区別は「Case」の「C」で表します。
アクセント（濁音、半濁音の有無）の区別は「Accent」の「A」で表します。
ひらがなとカタカナの区別は「Kana」の「K」で表します。
半角と全角の区別は「Width」の「W」で表します。
区別の有無
区別する場合は「Sensitive」の「S」で表します。
区別しない場合は「Insensitive」の「I」で表します。
照合順序の指定は区別の種類と区別の有無を組み合わせて表します。
大文字と小文字を区別するなら「CS」となり、区別しないなら「CI」となります。
「CI」だと「a」と「A」は同じになります。「CS」だと別になります。
「AI」だと「は”」、「ば」、「ぱ」は同じになります。「AS」だと別になります。
「KI」だと「か」と「カ」は同じになります。「KS」だと別になります。
「WI」だと「A」(半角)と「Ａ」(全角)は同じになります。「WS」だと別になります。
更に複数の照合順序を一度に指定する場合は「CI_AI」のようにアンダースコア「_」でつなげます。
照合順序の指定
大文字と小文字や濁音、半濁音の有無などのすべてを区別する場合は
「Japanese_CS_AS_KS_WS」
と指定します。
上記とは逆にすべてを区別しない場合は
「Japanese_CI_AI」
と指定します。
通常であれば
「Japanese_CI_AI_KI_WI」
となりそうですが、上記のように指定をするとエラーになります。
「KI（ひらがなとカタカナ）」と「WI（半角と全角）」については、区別しない場合は省略します。
区別する場合のみ「KS」「WS」を付加します。
「Japanese_CI_AI_KS」
「Japanese_CI_AI_WS」
「Japanese_CI_AI_KS_WS」
「Japanese_CS_AS_KS」
「Japanese_CS_AS_WS」
「Japanese_CS_AS_KS_WS」
「Japanese_CI_AS_KS」
「Japanese_CI_AS_WS」
「Japanese_CI_AS_KS_WS」
「Japanese_CS_AI_KS」
「Japanese_CS_AI_WS」
「Japanese_CS_AI_KS_WS」
検索条件に照合順序を指定する
SQLで検索条件を指定する際に照合順序を指定する場合は、COLLATE句を使います。
例えば「FIRST_NAME」という列に「George」という文字列をWHERE句で指定する場合に、大文字と小文字を区別しない場合は以下のようになります。
|
1 2 |
-- WHERE句の指定例 WHERE FIRST_NAME COLLATE Japanese_CI_AS_KS_WS = 'George' |
上記の例ではWHERE句で照合順序を指定していますが、JOIN句などで使用することもできます。
検索条件の指定例
以下に住所情報を保持するテーブルに対して、WHERE句を使用して検索条件を照合した場合の例を記載します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT * FROM [住所] -- 氏名は大文字と小文字、ひらがなとカタカナ、全角と半角、濁音と半濁音の有無を区別せずに検索 WHERE [都道府県カナ] COLLATE Japanese_CI_AI LIKE '%やまた%' -- 住所は大文字と小文字、ひらがなとカタカナ、全角と半角は区別せずに検索 AND [都道府県カナ] COLLATE Japanese_CI_AS LIKE '%トウキョウ%' AND [市町村カナ] COLLATE Japanese_CI_AS LIKE '%ちよだ%' AND [番地カナ] COLLATE Japanese_CI_AS LIKE '%ａ%' AND [番地カナ] COLLATE Japanese_CI_AS LIKE '%15%' AND [建物カナ] COLLATE Japanese_CI_AS LIKE '%ﾋﾞﾙ%' AND [建物カナ] COLLATE Japanese_CI_AS LIKE '%Ｆ%'; |
まとめ
照合順序の指定には以下の種類があります。
CI … Case Insensitive（大文字と小文字を区別しません。）
AI … Accent Insensitive（アクセントを区別しません。）
KI … Kana Insensitive（ひらがなとカタカナを区別しません。）
WI … Width Insensitive（半角と全角を区別しません。）
CS … Case Sensitive（大文字と小文字を区別します。）
AS … Accent Sensitive（アクセントを区別します。）
KS … Kana Sensitive（ひらがなとカタカナを区別します。）
WS … Width Sensitive（半角と全角を区別します。）
SC … Supplementary Character（SQL Server 2012 以降で、辞書順に並べる場合に補助文字を認識するかどうかを区別します。）
BIN … Binary（バイナリで比較します。すべてを区別します。）
検索実行時に照合順序を指定する場合は、WHERE句に続けて以下のように記述します。
〈照合対象の列〉 COLLATE Japanese_〈CI、ASなどの種類〉 = 〈検索条件の文字列〉
大文字と小文字、アクセント（濁音、半濁音の有無）、ひらがなとカタカナ、半角と全角をすべて区別する場合
|
1 2 |
WHERE [COLUMN] COLLATE Japanese_CS_AS_KS_WS = '検索条件' |
大文字と小文字、アクセント（濁音、半濁音の有無）、ひらがなとカタカナ、半角と全角をすべて区別しない場合
|
1 2 |
WHERE [COLUMN] COLLATE Japanese_CI_AI = '検索条件' |
