言語ソートを使用した大文字／小文字、全角／半角を区別しない文字列比較
Oracle10g からは言語ソートの機能が強化され、NLS_SORTパラメータに指定するソート名の末尾に'_CI'を付与することにより、大文字／小文字、全角／半角を区別しない文字列比較を実現できるようになっています。SELECT VALUE FROM T1 WHERE NLSSORT(VALUE,'NLS_SORT=Japanese_M_CI') = NLSSORT('a','NLS_SORT=Japanese_M_CI');
VALUE
------
A
a
Ａ
ａ
NLSSORT関数の戻り値を見ると、大文字／小文字、全角／半角（英数字、カタカナ）に加え、ひらがな／カタカナも区別していないことがわかります。
SELECT VALUE, NLSSORT(VALUE,'NLS_SORT=Japanese_M_CI') Japanese_M_CI FROM T1; VALUE JAPANESE_M_CI ------ ---------------- a 01F5000002 A 01F5000002 ａ 01F5000002 Ａ 01F5000002 1 017A000002 １ 017A000002 ｱ 3DBE000002 ア 3DBE000002 あ 3DBE000002 ﾊﾞ 3DDE3DBB00000202 バ 3DDE000002BD ば 3DDE000002BD ﾊﾟ 3DDE3DBC00000202 パ 3DDE000002C0 ぱ 3DDE000002C0 ! 0000 ※？ ！ 371D000001 " 0000 ※？ ” 3750000001 # 0000 ※？ ＃ 37FA000001 $ 0087000001 ※全角／半角が区別されている ＄ 396E000001 % 0000 ※？ ％ 3971000001 & 0000 ※？ ＆ 37FB000001
ただし、不完全な面もあります。
まず、濁点、半濁点を含む半角カタカナと全角カタカナが区別されます。
（1文字と2文字なので致し方ないところかもしれませんが．．．）
また、それ以上に厄介なのは記号です。
多くの半角記号のソート値がなぜか'0000'となっており、異なる記号なのに区別されないという妙な現象が発生します。
また、そうでない記号でも半角／全角が区別されている場合があります。
（UTF8の環境で試した結果なのですが、キャラクタ・セットの問題なんですかねぇ．．．）
この現象は、以下のようにTO_MULTI_BYTE関数を使って全角変換してからNLSSORT関数を使うと解消するようです。
SELECT VALUE, NLSSORT(TO_MULTI_BYTE(VALUE),'NLS_SORT=Japanese_M_CI') Japanese_M_CI FROM T1; VALUE JAPANESE_M_CI ------ ---------------- ! FFFFFE570000 ※全角変換不可 ！ 371D000001 " 3750000001 ” 3750000001 # 37FA000001 ＃ 37FA000001 $ 396E000001 ＄ 396E000001 % 3971000001 ％ 3971000001 & FFFFFE600000 ※全角変換不可 ＆ 37FB000001
（UTF8の環境で試したため、全角変換できない文字がありますが．．．）
また、LIKE検索がうまく機能しないようです。
例えば、以下のようなデータに対して、
VALUE ------------- Dog DOG dog Dog,Cat,Mouse DOG,CAT,MOUSE dog,cat,mouse Cat,Mouse,Dog CAT,MOUSE,DOG cat,mouse,dog Mouse,Dog,Cat MOUSE,DOG,CAT mouse,dog,cat Dog% DOG% dog% %Dog %DOG %dog %Dog% %DOG% %dog%
前方一致検索を行うと、
SELECT VALUE FROM T1
WHERE NLSSORT(TO_MULTI_BYTE(VALUE),'NLS_SORT=Japanese_M_CI')
LIKE NLSSORT(TO_MULTI_BYTE('dog%'),'NLS_SORT=Japanese_M_CI');
VALUE
-------------
Dog%
DOG%
dog%
後方一致検索を行うと、
SELECT VALUE FROM T1
WHERE NLSSORT(TO_MULTI_BYTE(VALUE),'NLS_SORT=Japanese_M_CI')
LIKE NLSSORT(TO_MULTI_BYTE('%dog'),'NLS_SORT=Japanese_M_CI');
VALUE
-------------
%Dog
%DOG
%dog
全文一致検索を行うと、
SELECT VALUE FROM T1
WHERE NLSSORT(TO_MULTI_BYTE(VALUE),'NLS_SORT=Japanese_M_CI')
LIKE NLSSORT(TO_MULTI_BYTE('dog%'),'NLS_SORT=Japanese_M_CI');
VALUE
-------------
%Dog%
%DOG%
%dog%
どうも'%'をただの文字として解釈しているようです。
※'%'のソート値が'0000'となり正常な比較ができないためTO_MULTI_BYTEを使用しています。
このように若干不完全ではありますが、これでも支障がなければ便利な機能だと言えるでしょう^^;
言語ソートを使用した大文字／小文字を区別しない文字列比較
Oracle10gで言語ソートの機能を使用して大文字／小文字のみを区別した文字列比較を行いたい場合は、NLS_SORTパラメータにソート名'Binary_CI'を指定します。SELECT VALUE FROM T1
WHERE NLSSORT(VALUE,'NLS_SORT=Binary_CI') = NLSSORT('a','NLS_SORT=Binary_CI');
VALUE
------
a
A
ただし、これもLIKE検索はうまく機能しません。
SELECT VALUE FROM T1
WHERE NLSSORT(VALUE,'NLS_SORT=Binary_CI')
LIKE NLSSORT('dog%','NLS_SORT=Binary_CI');
VALUE
-------------
Dog%
DOG%
dog%
SELECT VALUE FROM T1
WHERE NLSSORT(VALUE,'NLS_SORT=Binary_CI')
LIKE NLSSORT('%dog','NLS_SORT=Binary_CI');
VALUE
-------------
%Dog
%DOG
%dog
SELECT VALUE FROM T1
WHERE NLSSORT(VALUE,'NLS_SORT=Binary_CI')
LIKE NLSSORT('%dog%','NLS_SORT=Binary_CI');
VALUE
-------------
%Dog%
%DOG%
%dog%
NLS_SORTパラメータとNLS_COMPパラメータ
常に同じ言語ソートの設定でソートや文字列比較を行いたい場合は、システムレベル、またはセッションレベルでNLS_SORTパラメータ、及びNLS_COMPパラメータを設定しておくと便利です。NLS_SORTパラメータにソート名をセットしておけば、
ALTER [SYSTEM|SESSION] SET NLS_SORT = 'Japanese_M_CI';
NLSSORT関数にソート名を指定しなくてもNLS_SORTパラメータにセットしたソート名が使用されます。
さらに、NLS_COMPパラメータに'ANSI'を指定しておくと、
ALTER [SYSTEM|SESSION] SET NLS_COMP = 'ANSI';※10g リリース2からは'ANSI'ではなく、'LINGUISTIC'と指定するようです。
文字列比較を行う際にNLS_SORTパラメータの値に基づいた文字列比較が行われます。
SELECT VALUE FROM T1 WHERE VALUE = 'a'; VALUE ------ a A ａ Ａ
普通にSQLを書くだけで大文字／小文字、全角／半角を区別しない文字列比較ができるというわけです。
ちなみにLIKE検索を行うと．．．
SELECT VALUE FROM T1 WHERE VALUE LIKE 'dog%'; VALUE ------------- dog dog,cat,mouse dog%
LIKE検索としては機能していますが、言語ソートが機能していないように見えます．．．
10g リリース2のマニュアルを見ると、LIKE検索はNLS_COMPを'binary'としても'ANSI'としてもバイナリソートになってしまうようです。
10g リリース2からは'LINGUISTIC'を指定すれば言語ソートとなるようです。