【SQL Server 2005】Japanese_CI_AS の長音処理がおかしい
この記事は＠IT さんの Database Expert 会議室に投稿した記事のフォローアップです。
解決のために
問題の概要
SQL Server 2005 CTP June において，
Japanese_90_CI_AS，Japanese_CI_AS 照合順序を使用して，
LIKE 演算子に 「ー」（日本語長音）を含む２文字以上の文字列を処理すると
正しく抽出ができなくなる。
問題の再現
再現方法は Ｃ＃ MVP の中さんが簡潔にまとめてくださっています。
http://blogs.wankuma.com/naka/archive/2005/09/29/18540.aspx
ここでは，簡単におさらいを
varchar または，nvarchar を格納するテーブルを作成して，
「ー」（日本語長音）を含む２文字以上の文字列（ここでは「クー」）を条件とするクエリを発行します。
クエリは，実験するためのテーブルを準備して，実験コードを実行した後，後処理を実行します。
create database CollateTest
go
use CollateTest
go
create table JpnTest ( [Data] nvarchar(50) collate Japanese_CI_AS);
go
insert into JpnTest([Data]) values('クーリング')
insert into JpnTest([Data]) values('カーリング')
insert into JpnTest([Data]) values('ケビン')
select * from JpnTest where [Data] like '%クー%'
go
drop table JpnTest
go
create table JpnTest ([Data] nvarchar(50) collate Japanese_bin);
go
insert into JpnTest([Data]) values('クーリング')
insert into JpnTest([Data]) values('カーリング')
insert into JpnTest([Data]) values('ケビン')
select * from JpnTest where [Data] like '%クー%'
go
drop table JpnTest
go
期待される結果は，両方とも「クーリング」が返されることですが，
[Japanese_CI_AS] では期待する結果が返ってきません。
「ー」ではなく「〇」（漢数字の零）が長音文字になっている
結果的に，[Japanese_CI_AS] で，「クーリング」をヒットさせるには，
WHERE [Data] LIKE '%ク〇%'
として，「漢数字の〇」を長音文字としてあててあげなければなりません。
これは日本語の処理としては非常に不適切です。
SELECT [DATA]
FROM [CollateTest].[dbo].[JpnTest]
WHERE [DATA] LIKE '%ク〇%' COLLATE Japanese_90_CI_AS
SELECT [DATA]
FROM [CollateTest].[dbo].[JpnTest]
WHERE [DATA] LIKE '%ク〇%' COLLATE Japanese_BIN
GO
検証での考え方
さて，ここからはちょっと趣向を変えて，
「漢数字の零」が長音文字の代わりにヒットしてしまっている
ということを発見するまでに，TLC が何を考えたのか？
について書いてみたいと思います。
このなんでもない探求法を使ってみれば，みなさんでもおかしな動きの検証ができるようになるのではないでしょうか？
まず始めに，
[Japanese_CI_IS] は [SQL Server 2000] でもおかしな動きをしていた
という知識がありました。
特に長音記号に関する部分は，アルファベットなどの文字と組み合わせると不思議な動きをしてくれます。
この時から，「特定の文字」と「特定の文字」を組み合わせる状況によって「不審な動作をする」という経験的な知識が出来上がっていたのです。
この前提に立つと，
今回の動作も「ー」の代わりに何かの文字が代わりにヒットするのではないか？
という疑いが浮かんできます。
このアイデアが浮かびさえすれば，後は
総当り的に文字を試してみることで検証ができるのです。
実行したいことは，
「クー」の「ー」の部分をいろいろな文字に置き換えて，実際にクエリを投げてみてみることです
そうすると，
SQL ステートメントの nchar() 関数が使えそうだと思いつきます。
後は，nchar() 関数を使用して総当たり戦をうまく挑むだけですね。
実は，この辺は，それぞれの得意分野に持ち込んでもいいかも知れません。
つまり，ADO.NET を使用してクエリを組み立てるほうが，T-SQL ステートメントを操作するよりも得意な場合は，
通常のプログラミングの世界でループを組めばいいわけです。
ただし，このときには「追加で使用するテクノロジとのつなぎ目の部分で検証の範囲が広がってしまう」ということを念頭においておくことが必須です。
なるべくシンプルに検証できた方がよいわけですね。
さて，それでは実際に使用したコードです。
DECLARE @count int
DECLARE @limit int
DECLARE @CMD nvarchar(1000)
SET @count = 0
SET @limit = @count + 65535
WHILE @count < @limit
BEGIN
SET @count = @count + 1
SET @CMD = 'SELECT [DATA],'
+ CAST(@count AS nvarchar) +
' AS [char] FROM [TestDB].[dbo].[JpnTest] WHERE [DATA] LIKE ''%ク'
+ nchar(@count) + '%'' COLLATE Japanese_CI_AS'
EXEC sp_Executesql @CMD
END
GO
実行していることは，
クの直後の文字を総当り的に置き換えて，結果を返させているだけです。
結果の検証
このクエリを実行すると，クーリングをヒットさせる文字が３種類あることがわかります。
その３種類とは，
- 「_」（アンダーバー）
- 「%」（パーセント）
- 「〇」（漢数字の零）
です。
前半２つはなんてことはありません
単純にク「ー」リングが「任意の一文字」か「任意の文字列」にヒットしているだけです。
探していた問題の文字は漢数字の零だった，ということになりますね。
しかし，この検証には一つ問題がありまして，
クエリが先頭の１００結果セットしか画面表示しない
ことです。
このため，１００以上の結果セットでは，結果の件数しかわからないんですね。
（この場合は返ってくるかこないかがわかればよいので，さほど問題にはなりませんが）
よって，最初のクエリを投げた後，結果が返ってきている大まかな場所に
あたりをつけて，
もうすこし限定したクエリを投げます。
結果セットの表示文字列の 25000 行あたりに「（１行処理しました）」がありますから，問題は１２０００ぐらいだろうと予想するわけです。
DECLARE @count int
DECLARE @limit int
DECLARE @CMD nvarchar(1000)
SET @count = 12200
SET @limit = @count + 100
WHILE @count < @limit
BEGIN
SET @count = @count + 1
SET @CMD = 'SELECT [DATA],'
+ CAST(@count AS nvarchar) +
' AS [char] FROM [TestDB].[dbo].[JpnTest] WHERE [DATA] LIKE ''%ク'
+ nchar(@count) + '%'' COLLATE Japanese_CI_AS'
EXEC sp_Executesql @CMD
END
GO
こうすることで，問題の部分の結果を得ることができました。
このように，わりとシンプルなアイデアを実現することによって，実現できる部分が多くあると思います。
みなさんもぜひトライしてみてください。
この記事は Microsoft SQL Server 2005 CTP June
に基づいて記述しています。