SQL Server 2005で照合順序（SQL_EBCDIC037_CP1_CS_AS）の対応している文字コードを教えてください。「-」は対象外でしょうか？

Question

先日Microsoft Answersに投稿した内容になります。
http://social.answers.microsoft.com/Forums/ja-JP/vistaassistantja/thread/a4efc12b-201b-4b19-ab3d-0109c40b5789?prof=required

そちらの回答により、こちらのフォーラムの存在を教えていただきましたので、こちらに転記させていただきました。
以下転用です。

***************************************************************************************************************

はじめての投稿になります。
足りない情報や不適切な質問をしていた場合にはご指摘いただければ幸いです。
ご回答よろしくお願いいたします。

【環境】
OS:Windows XP Professional version2002 Service Pack 3

Microsoft SQL Server Management Studio:9.00.1399.00
Microsoft Analysis Services クライアント ツール:2005.090.1399.00
Microsoft Data Access Components (MDAC) :2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML:2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer:7.0.5730.13
Microsoft .NET Framework:2.0.50727.3082
オペレーティング システム:5.1.2600


【詳細】
照合順序にSQL_EBCDIC037_CP1_CS_ASを指定したときに、
varchar(25)で「FEN」と「FEN-」というデータが入っていた場合に、「FEN-」が先に表示されてしまいます。
（SQL_EBCDIC037_CP1_CS_ASを指定しない場合には「FEN」が先に表示されます。）

「-」はEBCDICを指定した場合、ソートの対象コードではないのでしょうか？
また「-」以外にも対象とならない記号はあるのでしょうか？
対応している文字コード一覧のようなものがあればうれしいです。

また下に示しました「欲しい結果」と同じ結果が得られる方法があれば教えていただければ幸いです。
「欲しい結果」のように並び変えができる指定がないのであれば、「ない」ということを知りたいです。
（クエリの大幅な変更により可能という場合には「ない」という判断でOKです。オプションで指定する程度で変更可能なものがあれば教えてください。）

【例】
テーブル名：TBL_Products

カラム：
・ProductCode varchar(25)
・ProductName varchar(50)

データ：
[ProductCode],[ProductName]
FEN,フェンネル
FEN1,フェンネル１
FENA,フェンネルA
FEN-,フェンネルマイナス

実行するクエリ
select ProductCode from TBL_Products
order by ProductCode
COLLATE SQL_EBCDIC037_CP1_CS_AS

検索結果
FEN-,フェンネルマイナス
FEN,フェンネル
FENA,フェンネルA
FEN1,フェンネル１

欲しい結果
FEN,フェンネル
FEN-,フェンネルマイナス
FENA,フェンネルA
FEN1,フェンネル１

***************************************************************************************************************

anningo さんより回答
***************************************************************************************************************

御質問についてちゃんと回答できる知識を持っていないためわかることだけ書きます。

とりあえず例題のデータだけ考えるなら、オプションではないですが↓で期待する結果が得られます。

select ProductCode from TBL_Products

order by Len(ProductCode), ProductCode

COLLATE SQL_EBCDIC037_CP1_CS_AS

あと、不思議なことに

ProductCode を [varchar]→[nvarchar]にすると期待する結果が得られました。

なぜそうなるのか解りません。。

もし Answers で回答が得られなかった場合、開発者向けのフォーラムで質問されるとよいかもしれません

SQL Server デベロッパー センター > SQL Server フォーラム > SQL Server

http://social.msdn.microsoft.com/Forums/ja-JP/sqlserverja/threads

***************************************************************************************************************

私の返信
***************************************************************************************************************

anningoさん
ご回答ありがとうございます。

記述していただいたクエリなら例で載せたパターンは解決できますね！
ただ例には載せていなかったパターン「FENA1,フェンネルA1」のようなデータが入ってきた場合には「欲しい結果」には至りませんでした。
情報の足りない例にも関わらず考えていただいてありがとうございました。

結果
FEN,フェンネル
FEN-,フェンネルマイナス
FENA,フェンネルA
FEN1,フェンネル１
FENA1,フェンネルA1

欲しい結果
FEN,フェンネル
FEN-,フェンネルマイナス
FENA,フェンネルA
FENA1,フェンネルA1
FEN1,フェンネル１

>ProductCode を [varchar]→[nvarchar]にすると期待する結果が得られました。
>なぜそうなるのか解りません。。

試してみたのですが、どうやら「アルファベット」と「数値」が逆になるEBCDICの順番ではなく、普通の文字列の順番でソートされているようでした。
COLLATE SQL_EBCDIC037_CP1_CS_AS
nvarcharだと↑の照合順序が適用されないんですかねぇ。
ますます不思議です・・・

>もし Answers で回答が得られなかった場合、開発者向けのフォーラムで質問されるとよいかもしれません

そういったところもあるのですね。
全然知らずに投稿してしまいました・・・
しばらく様子を見て回答が得られなかったら、そちらにも投稿してみようと思います。

ありがとうございました。
***************************************************************************************************************

以上になります。

よろしくお願いいたします。

Answer 1

こんな

select ProductCode from TBL_Products
order by (ProductCode + char(0))
COLLATE SQL_EBCDIC037_CP1_CS_AS


#後ろにスペースがくっついてるような動作してるから、char(0)で切ってみた

Answer 2

問題の解決策ではありませんが・・・

> COLLATE SQL_EBCDIC037_CP1_CS_AS
> nvarcharだと↑の照合順序が適用されないんですかねぇ。
> ますます不思議です・・・

SQL 照合順序を使用した場合、Unicode データと非 Unicode データでは適用される並べ替
え規則（ハイフンの扱い）が異なるようです。

SQL の照合順序と Windows の照合順序の比較
http://support.microsoft.com/kb/322112

上記のページの説明で、nvarchar の場合の結果は納得できるのですが、varchar の場合に
FEN- が FEN より小さいと判断されるのが不思議です。そういう並べ替え規則になってい
るということなんでしょうか？？？

Answer 3

こんにちは、nagino です。

あまり文字コードまわりは詳しくないのですが・・・。

Unicode の時の挙動については SurferOnWww 様の記載のとおりです。

EBCDIC の CP1 ということは Code Page が 1252 なので、コード表は以下でしょうか。

http://www-03.ibm.com/systems/resources/systems_i_software_globalization_pdf_cp01252z.pdf

SQL_EBCDIC037_CP1_CS_AS について、取り急ぎ手元の環境で確認しました。

空文字と比較した際に、空文字より前にくる文字を以下のクエリで確認しました。

--本質外の箇所は省略しています

DECLARE @n int

SET @n = 1

WHILE @n <= 255

BEGIN

IF CHAR(@n) < '' COLLATE SQL_EBCDIC037_CP1_CS_AS

--結果を出力、もしくは一時テーブルに格納

SET @n = @n + 1

END

結果、環境1(Windows XP SP3 / SQL Server 2005 SP2 (9.0.3073)) では以下の計 95 文字が空文字より前に来るという動きをしますね。

 CHAR(1) ～ CHAR(31)

 CHAR(45) いわゆる「-」

 CHAR(94) いわゆる「^」

 CHAR（126） いわゆる「~」

 CHAR(127)

 CHAR(129）～CHAR(159)

 CHAR(224)～CHAR(252)

環境2(Windows Server 2008 SP1 / SQL Server 2005 SP2 (9.0.3042)) では以下の計 35 文字が空文字より前に来るという動きをしますね。

 CHAR(1) ～ CHAR(31)

 CHAR(45) いわゆる「-」

 CHAR(94) いわゆる「^」

 CHAR（126） いわゆる「~」

 CHAR(127)

環境3(Windows Server 2008 SP1 / SQL Server 2008 RTM (10.0.1600)) も環境2 と同様でした。

コード表上に存在しない文字についてソート順で考慮されない状態なのは理解できますが、「-」「^」「~」がソート時に考慮されないのはしっくりきませんね。

---

MCITP（Database Developer/Database Administrator）

Answer 4

gekkaさん

早速のご回答ありがとうございます。

ソート順は解決できましたが、理由がわからないため本番では使えなそうです・・・
でもありがとうございます！

Answer 5

SurferOnWwwさん

参考になりました。
ありがとうございます。

リンク先による以下の記述が理由なのでしょうが、正直よくわかりませんでした・・・
>たとえば、SQL 照合順序 "SQL_Latin1_General_CP1_CI_AS" を使用している場合、非 Unicode 文字列の 'a-c' は文字列 'ab' よりも小さくなります。これは、ハイフン ("-") が "b" の前に出現する独立した文字として並べ替えが行われるためです。

「独立した文字列」というのがキーのようですね。

難しいです。

Answer 6

naginoさん、こんにちは。

コード表を教えていただきましてありがとうございます。
確かにコード表に載っているのにソートされないのは残念ですね・・・


また環境により空文字の順番まで調べていただき本当にありがとうございます。

naginoさんに確認していただいた結果を見る限りでは、何か手を加えて（たとえばgekkaさんに教えていただいた方法など）
ソートのオプションとしてSQL_EBCDIC037_CP1_CS_ASを使用するのは危険そうですね。

現在のところ簡単にはソートを実現できないということで共有しておきたいと思います。

ありがとうございました。

Answer 7

こんにちは、nagino です。

この件、とりあえず Microsoft Connect の方でもレポートをあげておきました。

ただ個人的にも気になるので、私のほうからサポートの方に
「フォーラムでこういう話題があるのだが、どうしてそのような挙動になるのか教えてほしい」
という感じで問い合わせてみようと思いますが、いかがでしょうか？

---

MCITP（Database Developer/Database Administrator）

Answer 8

naginoさん、こんにちは。

ご配慮いただきましてありがとうございます。
私の調査・問い合わせ不足によりお手数をおかけして申し訳ございませんが、お問い合わせいただけましたら幸いです。

こちらでもわかったことがありましたら、ご報告させていただきます。

よろしくお願いいたします。

Answer 9

こんにちは、nagino です。

SR(Service Request) オープンしました。

開発チームの調査結果を貰えることになっていますので、とりあえず 2 週間ぐらい待ってください。

取り急ぎご連絡まで。

---

MCITP（Database Developer/Database Administrator）

Answer 10

naginoさん、おはようございます。

ご連絡ありがとうございます。
なかなかわけわかんない問題ですね・・・

単純に不具合な気がしないでもありませんが。

Answer 11

こんにちは、nagino です。

MS のサポートから連絡がありました。

SQL Server の EBCDIC では、MS 定義の順序で並び替えるようになっていて、IBM とは異なるようになっているそうです。

また、IBM のソート順と一緒になる照合順序は無いそうです。

不具合ということではなく、IBM と一致させる気がそもそも無く、そういうデザインだということだそうです。

IBM が定義した EBCDIC と異なるのに、EBCDIC と名前をつけるあたりはちょっとどうかという感じですが・・・。

---

MCITP（Database Developer/Database Administrator）

Answer 12

naginoさん、こんにちは。

ご連絡ありがとうございます。

>IBM が定義した EBCDIC と異なるのに、EBCDIC と名前をつけるあたりはちょっとどうかという感じですが・・・。
ややこしいですね。
しかも微妙に似せてるあたりがなんとも言えないですね・・・
仕様検討ミスとしか思えませんよね。。。

本当にありがとうございました！

Answer 13

こんにちは、フォーラムオペレーターの高橋春樹です。

gekkaさん、SuferOnWwwさん、naginoさん
いつもお世話になっております。

naginoさん、
弊社サポートへの問い合わせ、情報の展開、有難うございます。
このフォーラムを閲覧している方にとっても有用な情報になるので助かります。

LOAIVEYUさん、初めまして。
MSDNフォーラムのご利用有難うございます。
ご指摘の点については、MSのデザインということで・・・ご理解の程宜しくお願いします。
今回、皆様からの投稿が、有用な情報だと思いましたので、勝手ながら、回答マークを付けさせて貰いました。

今後ともMSDNフォーラムを宜しくお願いします。

---

マイクロソフト株式会社 フォーラム オペレーター 高橋春樹

Answer 14

こんにちは、nagino です。


MS のサポートと色々相談、交渉していて遅くなりました。
電話で色々相談してお願いしたところ、追加情報が貰えました。


まず、今回の比較のロジックですが、前提として ANSI で規定されているルールに則り、スペース埋めで長さを揃えてから比較するそうです。
（gekka 様のご指摘どおりです）
このあたりの挙動は以下に記載があるそうです。
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316626


ですので、たとえば 'A' と 'A-' を比較した場合は、'A ' と 'A-' の比較になるそうです。


一方 EBCDIC のソートテーブル（各文字のソートの重み付けの定義）では、いくつかの文字がスペースより前に来るよう定義されていたため、今回の結果となったそうです。


ソートの前後関係は、このようにスペース埋めと文字の大小関係から導かれます。
スペース埋めは前述した KB にもありますし、ルール自体は単純です。
また、先に私が提示したクエリで個々の文字同士の大小関係が取得できますので、かなり大変ですが頑張れば独自に調べることはできそうです。


ちなみに、今回問題とした照合順序のソートテーブルの内容は上記ロジックの裏づけとしてサポートから頂くことができました。
特定の SQL 照合順序のソートテーブルについては、有償サポートに依頼すると貰えるかもしれません。（貰えるかどうかは保障できませんが・・・）
ただ、最近の照合順序は Windows 照合順序（OS が持っているソートテーブルをそのまま利用している）なのと、Unicode になると文字数が飛躍的に多くなるので、そういった場合はちょっと難しいかと思います。


また、ハイフンや類似の記号は多数ありますが、ソートテーブルにも複数の半角ハイフンが定義されていました。
そのため、'-' を 0x2D ではなく 0x97 にマップするとSQL Server においても IBM の定義したコード順でソートすることが可能です。
メインフレームではスペース埋めで比較するかどうかはちょっと確認できる状況にないのですが、そこが同じであれば、EBCDIC を使用しているメインフレームから移行する際でも、既存データの並び順が変わるわけではないようです。
以前の投稿で記載した「IBM のソート順と一緒になる照合順序は無い」というのは不正確なようです。
以上補足して終わりとさせてください。


サポートには非公開情報ぎりぎりまで譲歩、公開していただけましたが、これ以上の情報提供は無理ということで、SR はクローズしたいと思います。
私も色々と勉強になりました。

---

MCITP（Database Developer/Database Administrator）