SQLで大文字小文字、半角全角、ひらがなカタカナなどが混在した文字列を並び替えたり、比較したりするとき、思ったとおりに動作してくれなくて困ることがあります。
これは比較や並べ替えのルールがRDBMSや設定によって様々で、そのルールが自分の望むものではないというときにしばしば出くわす問題です。
そこで、実際にOracle、SQL Serverそれぞれで大文字小文字、全角半角、ひらがなカタカナがどのように並べ替えられるかを見てみます。
また、並べ替えのルールを変更する方法についても見てみます。
Oracle
Oracleでは文字列の比較や並べ替えはコード順で行います。
そのため、大文字小文字、全角半角、ひらがなカタカナが混在したような列の場合、不自然な並び順となることがあります。
SELECT COLUMN1 FROM TABLE1 ORDER BY COLUMN1 COLUMN1 ------- A AA B BB a aa b bb あ い ア イ ｱ ｲ
でも、これをどうしても辞書順に並べたい、大文字であろうが小文字であろうが全角であろうが半角であろうがアルファベット順または50音順に並べたいという場合もあるかもしれません。
そんなときにはNLSSORT関数を使えば、辞書順の並べ替えができるようです。
SELECT COLUMN1 FROM TABLE1 ORDER BY NLSSORT(COLUMN1,'NLS_SORT=Japanese') COLUMN1 ------- a A aa AA b B bb BB あ ア ｱ い イ ｲ
また、同じテーブルに対して、以下のような比較条件で検索した場合、何も返ってこないですが、
SELECT COLUMN1 FROM TABLE1 WHERE A BETWEEN 'a' AND 'BB' COLUMN1 -------
これもNLSSORT関数を使うと大文字小文字関係なく'a'から'BB'までの文字列が返ってくるようになります。
SELECT COLUMN1 FROM TABLE1
WHERE NLSSORT(A,'NLS_SORT=Japanese')
BETWEEN NLSSORT('a','NLS_SORT=Japanese') AND NLSSORT('BB','NLS_SORT=Japanese')
COLUMN1
-------
a
A
aa
AA
b
B
bb
BB
SQL Server
SQL Serverでは並べ替えのルールはWindows照合順序によって決まります。
Windows照合順序では関連付けられたWindowsロケールの規則に従うか、コード順で並べ替えるかを選択することができるのですが、デフォルトでは既定のWindowsロケールの規則に従う設定となっているため、以下のように辞書順で並べ替えられるということになります。
カナの並び順がOracleとは微妙に異りますが．．．
SELECT COLUMN1 FROM TABLE1 ORDER BY COLUMN1 COLUMN1 -------- a A aa AA b B bb BB ｱ あ ア ｲ イ い
これをどうしてもコード順に並べたいというときにはCOLLATE句で[Windowsロケール]_BINを指定すればOKです。
SELECT COLUMN1 FROM TABLE1 ORDER BY COLUMN1 COLLATE JAPANESE_BIN COLUMN1 -------- A AA B BB a aaa b bb あ い ア イ ｱ ｲ
比較に関しても同じで、COLLATE句を使用すればコードで比較することができます。
また、Windows照合順序はインスタンス、データベースレベルでも設定できるため、インスタンスまたはデータベース全体でコード順に並べ替える設定も可能です。
以上、OracleとSQL Serverで並び順を比較してみましたが、まず標準の状態で両者の並べ替えのルールが異なるということは置いといて、両者とも並び順が問題になったときにそのルールを変更する手立ては用意されているので、どうしても問題があるのでルールを変えたいというときにも何とか対応はできます。
ただ、並べ替えや比較のたびにこんなコーディングをするのは非常に面倒ではないでしょうか？
こんなことで四苦八苦するくらいならコード順に並べ替えられることによって支障をきたすようなデータの登録をしないようにする方が賢明ではないでしょうか？
つまり、必ず大文字で登録するとか、フリガナなどのソート用の列を設けるとか．．．いろいろ手はあるはずです。