コメント
コメントの投稿
※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。
入力されていないコメントには返信しませんのであらかじめご了承くださいませ。
全角と半角と大文字と小文字を区別せずに検索する方法 -照合順序- | SQL Server
- 2019/05/15
- 22:52
今回は SQL Server で正しく照合順序が設定されていないが故に、SQL でのあいまい検索等を実行した際に意図しない結果となることがあります。通常、データベースを作成する際に、全角と半角、大文字と小文字を区別しない照合順序である Japanese_CI_AS で設定することがセオリーとされていますが、昔のシステムやデフォルトのままだと区別するような設定になっていることがあります。
そこで全角・半角・大文字・小文字が区別されてしまわないように検索用のデータフィールドを設けたり、全角や大文字に変換（または半角・小文字に変換）して検索するという手法が横行しております。SQL だけで実現するには大文字・小文字は UPPER / LOWER で実現可能ですが、全角・半角は SQLCLR を使用しないと実現が困難です。そのため、そのような力技を用いなくても照合順序を指定して検索することができる機能がありますので、今回は照合順序を指定して検索する方法をご紹介したいと思います。
前提条件
・Windows 7 professional 以降 / Windows Server 2008 R2 以降
・SQL Server 2008 Developer / Standard / Enterprise 以降
１．照合順序
SQL Server の場合、Japanese_CS_AS_KS_WS または Japanese_CI_AS のように記述します。S (Sensitive) は区別し、I (Insensitive) は区別しません。データベースのプロパティからデータベース毎の照合順序設定が確認できます。照合順序につきまして詳しくは以下のURLをご確認ください。
https://blogs.msdn.microsoft.com/jpsql/2016/07/26/1-3/
また、SQL 内に使用できる照合順序は以下のSQLで確認ができます。
SELECT name, description FROM fn_helpcollations() WHERE name like 'Japanese%'
;
２．検索方法
検索するフィールドのすぐ後に COLLATE と共に記述します。（１）全角・半角・大文字・小文字を区別せずに検索する場合
Japanese_CI_AS ※ KI_WI は省略されています。
（２）全角・半角は区別するが、大文字・小文字を区別せずに検索
Japanese_CI_AS_KS_WS
（３）全角・半角は区別せずに、大文字・小文字を区別する検索
Japanese_CS_AS ※ KI_WI は省略されています。
USE testDB
GO
--全角・半角・大文字・小文字を区別せずに検索
SELECT *
FROM [testDB].[dbo].[KEN_ALL]
WHERE [都道府県カナ] COLLATE Japanese_CI_AS LIKE '%トウキョウ%'
AND [市町村カナ] COLLATE Japanese_CI_AS LIKE '%ミナト%'
AND [番地カナ] COLLATE Japanese_CI_AS LIKE '%a%'
AND [番地カナ] COLLATE Japanese_CI_AS LIKE '%（%'
AND [番地カナ] COLLATE Japanese_CI_AS LIKE '%１%'
;
--全角・半角は区別するが、大文字・小文字を区別せずに検索
SELECT *
FROM [testDB].[dbo].[KEN_ALL]
WHERE [都道府県カナ] COLLATE Japanese_CI_AS_KS_WS LIKE '%ﾄｳｷｮｳ%'
AND [市町村カナ] COLLATE Japanese_CI_AS_KS_WS LIKE '%ﾐﾅﾄ%'
AND [番地カナ] COLLATE Japanese_CI_AS_KS_WS LIKE '%a%'
AND [番地カナ] COLLATE Japanese_CI_AS_KS_WS LIKE '%(%'
AND [番地カナ] COLLATE Japanese_CI_AS_KS_WS LIKE '%1%'
;
--全角・半角は区別せずに、大文字・小文字を区別する検索
SELECT *
FROM [testDB].[dbo].[KEN_ALL]
WHERE [都道府県カナ] COLLATE Japanese_CS_AS LIKE '%トウキョウ%'
AND [市町村カナ] COLLATE Japanese_CS_AS LIKE '%ミナト%'
AND [番地カナ] COLLATE Japanese_CS_AS LIKE '%a%'
AND [番地カナ] COLLATE Japanese_CS_AS LIKE '%（%'
AND [番地カナ] COLLATE Japanese_CS_AS LIKE '%１%'
;
※データは郵便局公式ページからダウンロードできる都道府県のデータを使用して検索しています。前回の記事と同様にプライマリキーやインデックスを作成していないテーブル構成となっています。
最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。
- 関連記事
-
- 英語の月名を含む日付の変換方法 | SQL Server
- 全角と半角と大文字と小文字を区別せずに検索する方法 -照合順序- | SQL Server
- IN 句と EXISTS 句で実行速度が速いのはどちらの方か検証してみました | SQL Server
- 複数トリガーの実行順序を検証してみました | SQL Server
- varchar をデータ型 numeric または decimal に変換中に、算術オーバーフロー エラーが発生しました。| SQL Server
一意のキー属性 'value' が 'default.aspx'に設定されている種類 'add' の重複コレクション エントリを追加できません - Web.config の設定- | asp.net ホーム
非同期スレッドからコントロールオブジェクトを操作する方法 | Xamarin.Forms
