fc2ブログ

記事一覧

全角と半角と大文字と小文字を区別せずに検索する方法 -照合順序- | SQL Server


今回は SQL Server で正しく照合順序が設定されていないが故に、SQL でのあいまい検索等を実行した際に意図しない結果となることがあります。通常、データベースを作成する際に、全角と半角、大文字と小文字を区別しない照合順序である Japanese_CI_AS で設定することがセオリーとされていますが、昔のシステムやデフォルトのままだと区別するような設定になっていることがあります。

そこで全角・半角・大文字・小文字が区別されてしまわないように検索用のデータフィールドを設けたり、全角や大文字に変換(または半角・小文字に変換)して検索するという手法が横行しております。SQL だけで実現するには大文字・小文字は UPPER / LOWER で実現可能ですが、全角・半角は SQLCLR を使用しないと実現が困難です。そのため、そのような力技を用いなくても照合順序を指定して検索することができる機能がありますので、今回は照合順序を指定して検索する方法をご紹介したいと思います。


sqlserver_collate_01.png



前提条件
・Windows 7 professional 以降 / Windows Server 2008 R2 以降
・SQL Server 2008 Developer / Standard / Enterprise 以降



1.照合順序

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%'
;

sqlserver_collate_02.png



2.検索方法

検索するフィールドのすぐ後に COLLATE と共に記述します。
(1)全角・半角・大文字・小文字を区別せずに検索する場合
   Japanese_CI_AS ※ KI_WI は省略されています。
(2)全角・半角は区別するが、大文字・小文字を区別せずに検索
   Japanese_CI_AS_KS_WS
(3)全角・半角は区別せずに、大文字・小文字を区別する検索
   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 '%1%'
;

--全角・半角は区別するが、大文字・小文字を区別せずに検索
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 '%1%'
;


sqlserver_collate_03.png

※データは郵便局公式ページからダウンロードできる都道府県のデータを使用して検索しています。前回の記事と同様にプライマリキーやインデックスを作成していないテーブル構成となっています。




最後までお読みいただき、ありがとうございます。
他にも SQL Server に関する記事をご紹介しておりますのでご参考頂ければ幸いです。


関連記事

コメント

コメントの投稿

※名前とタイトルが入力されていないコメントでは他のコメントとの区別ができません。

 入力されていないコメントには返信しませんのであらかじめご了承くださいませ。

※ニックネームでも良いので必ずご入力ください。

    

※必ずご入力ください。

    
    

※必ずご入力ください。

※技術的な質問には環境やエラーについて正確かつ詳細にお教えください。

・正確なエラーの内容

・Windowsのバージョン番号

・Visual Studioのバージョン

・機器の型番

・アプリやソフトのバージョン

    

カテゴリ別記事一覧

広告

プロフィール

石河 純


著者名 :石河 純
自己紹介:素人上がりのIT技術者。趣味は卓球・車・ボウリング

IT関連の知識はざっくりとこんな感じです。
【OS関連】
WindowsServer: 2012/2008R2/2003/2000/NT4
Windows: 10/8/7/XP/2000/me/NT4/98
Linux: CentOS RedHatLinux9
Mac: macOS Catalina 10.15 / Mojave 10.14 / High Sierra 10.13 / Sierra 10.12 / OSX Lion 10.7.5 / OSX Snow Leopard 10.6.8
【言語】
VB.net ASP.NET C#.net Java VBA
Xamarin.Forms
【データベース】
Oracle 10g/9i
SQLServer 2016/2008R2/2005/2000
SQLAnywhere 16/11/8
【BI/レポートツール】
Cognos ReportNet (IBM)
Microsoft PowerBI
ActiveReport (GrapeCity)
CrystalReport
【OCX関連】
GrapeCity InputMan SPREAD MultiRow GridView
【ネットワーク関連】
CCNP シスコ技術者認定
Cisco Catalyst シリーズ
Yamaha RTXシリーズ
FireWall関連
【WEB関連】
SEO SEM CSS jQuery IIS6/7 apache2

休みの日は卓球をやっています。
現在、卓球用品通販ショップは休業中です。