Transact SQL (SQL Server) でlike 演算子で検索すると 全角カタカナの検索でひらがなが検索結果に含まれる - SQL Server

Transact SQL (SQL Server) でlike 演算子で検索すると 全角カタカナの検索でひらがなが検索結果に含まれる現象について紹介します。

現象の確認

下図のテーブルを準備します。

id	category	product_name
1	キャンディ	クールキャンディ
2	チョコレート	カカオ99.9%
3	ケーキ	みるくDeショート
4	キャンディ	もふもふキャンディ
5	キャンディ	レモンですっぱー
6	チョコレート	マカダミアンナッツチョコ
7	チョコレート	カカオ99.9%
8	キャンディ	ミルク濃厚キャンディ

下記のSQLを実行します。

select * from ProductsC where product_name like '%ミルク%'

下図の結果が表示されます。"product_name"列に。カタカナの「ミルク」で始まる検索クエリですが、ひらがなの「みるく」で始まるレコードも検索結果に含まれています。

対処法

カタカナのみを検索対象にする場合は、COLLATE句を利用して照合順序を設定します。
下記のSQLに変更します。

select * from ProductsC where product_name COLLATE Japanese_CI_AS_KS_WS like '%ミルク%'

COLLATE Japanese_CI_AS_KS_WS が照合順序の指定になります。Japanese_CI_AS_KS_WS を設定することで、 「大文字小文字を区別しない、アクセントを区別する、かなを区別する、文字幅を区別する」検索条件でレコードを検索します。

SQLを実行します。かなを区別するため、ひらがなの「みるく」のレコードは該当せず、検索結果には表示されません。

参考:COLLATE オプションで指定できる値

COLLATE オプションで指定できる値には以下があります。
なお、"Japanese"の部分は日本語でない場合は別の言語名に変わります。(日本語の場合は Japanese_* の利用で問題ありません。)

COLLATE オプションの値の種類

オプション	意味
Japanese_BIN	バイナリ並べ替え
Japanese_BIN2	バイナリコード ポイント並べ替え順
Japanese_CI_AI	大文字小文字を区別しない、アクセントを区別しない、かなを区別しない、文字幅を区別しない
Japanese_CI_AI_KS	大文字小文字を区別しない、アクセントを区別しない、かなを区別する、文字幅を区別しない
Japanese_CI_AI_KS_WS	大文字小文字を区別しない、アクセントを区別しない、かなを区別する、文字幅を区別する
Japanese_CI_AI_WS	大文字小文字を区別しない、アクセントを区別しない、かなを区別しない、文字幅を区別する
Japanese_CI_AS	大文字小文字を区別しない、アクセントを区別する、かなを区別しない、文字幅を区別しない
Japanese_CI_AS_KS	大文字小文字を区別しない、アクセントを区別する、かなを区別する、文字幅を区別しない
Japanese_CI_AS_KS_WS	大文字小文字を区別しない、アクセントを区別する、かなを区別する、文字幅を区別する
Japanese_CI_AS_WS	大文字小文字を区別しない、アクセントを区別する、かなを区別しない、文字幅を区別する
Japanese_CS_AI	大文字小文字を区別する、アクセントを区別しない、かなを区別しない、文字幅を区別しない
Japanese_CS_AI_KS	大文字小文字を区別する、アクセントを区別しない、かなを区別する、文字幅を区別しない
Japanese_CS_AI_KS_WS	大文字小文字を区別する、アクセントを区別しない、かなを区別する、文字幅を区別する
Japanese_CS_AI_WS	大文字小文字を区別する、アクセントを区別しない、かなを区別しない、文字幅を区別する
Japanese_CS_AS	大文字小文字を区別する、アクセントを区別する、かなを区別しない、文字幅を区別しない
Japanese_CS_AS_KS	大文字小文字を区別する、アクセントを区別する、かなを区別する、文字幅を区別しない
Japanese_CS_AS_KS_WS	大文字小文字を区別する、アクセントを区別する、かなを区別する、文字幅を区別する
Japanese_CS_AS_WS	大文字小文字を区別する、アクセントを区別する、かなを区別しない、文字幅を区別する

参考:半角カタカナの場合

半角カタカナの場合の動作も確認します。
下記のSQLを実行します。

select * from ProductsC where product_name like 'ﾐﾙｸ%'

下図の結果が表示されます。全角のカタカナ、ひらがなの両方が検索結果に表示されます。


SQL文を下記に変更します。 COLLATE Japanese_CI_AS_KS_WS を指定します。

select * from ProductsC where product_name COLLATE Japanese_CI_AS_KS_WS like 'ﾐﾙｸ%'

SQLを実行数ると下図の表示結果になります。半角と全角が区別されるため、検索結果には該当しなくなります。


SQL文を下記に変更します。COLLATE Japanese_CS_AI_KS を指定します。

select * from ProductsC where product_name COLLATE Japanese_CS_AI_KS like 'ﾐﾙｸ%'

SQLを実行数ると下図の表示結果になります。半角と全角は区別されませんが、かなは区別されるため、全角のカタカナは検索結果に該当します。