NLSSORT ファンクション(辞書順ソート)

辞書順ソート( NLSSORT )

書式
NLSSORT(char, 'NLS_SORT=sort')

文字列を大文字小文字、半角全角、濁点半濁点を考慮した辞書順となる値(バイナリ: RAW)を戻す。ソート項目や条件項目に使用し、 直接利用することはない(SELECT の抽出項目に指定してもバイナリなので、あまり価値がない。)
第 2パラメータは言語ソート基準(デフォルト BINARY) を指定する。
日本語環境においては 'JAPANESE_M('JAPANESE')|BINARY' の二択だと思われる。
Oracle 10g から 'JAPANESE' は廃止となっているが指定は可能(サポートしないと思われる)。

以下の(1), (2), (3) の例は同じ検索結果になる。

大文字小文字の区別を行わないようにする場合。('a', 'b' の変化に注目)

-- (1)
WHERE
      NLSSORT(col,'NLS_SORT=JAPANESE_M') >= NLSSORT('a','NLS_SORT=JAPANESE_M')
  AND NLSSORT(col,'NLS_SORT=JAPANESE_M') < NLS_SORT('b','NLS_SORT=JAPANESE_M')
-- (2)
WHERE
      NLSSORT(col,'NLS_SORT=JAPANESE_M') >= NLSSORT('A','NLS_SORT=JAPANESE_M')
  AND NLSSORT(col,'NLS_SORT=JAPANESE_M') < NLSSORT('b','NLS_SORT=JAPANESE_M')
-- (3)
WHERE
      NLSSORT(col,'NLS_SORT=JAPANESE_M') >= NLSSORT('A','NLS_SORT=JAPANESE_M')
  AND NLSSORT(col,'NLS_SORT=JAPANESE_M') < NLSSORT('B','NLS_SORT=JAPANESE_M')

全角、半角、濁点、半濁点を考慮したい場合 Oracle 9i ( 8i ではマルチバイト・キャラクタセット未対応)

WHERE
      NLSSORT(col,'NLS_SORT=JAPANESE_M') >= NLSSORT('あ','NLS_SORT=JAPANESE_M')
  AND NLSSORT(col,'NLS_SORT=JAPANESE_M') < NLSSORT('い','NLS_SORT=JAPANESE_M')
--
WHERE
      NLSSORT(col,'NLS_SORT=JAPANESE_M') >= NLSSORT('ア','NLS_SORT=JAPANESE_M')
  AND NLSSORT(col,'NLS_SORT=JAPANESE_M') < NLSSORT('イ','NLS_SORT=JAPANESE_M')

ソートに使用する場合

ORDER BY NLSSORT(col,'NLS_SORT=JAPANESE_M')

条件だけでなく、半角カナをソートキーに使用している場合には、ファンクションインデックスの作成を検討する。 検索時には、かなりの高速化が期待できる。⇒ ファンクション・インデックスの作成

大文字、小文字、半角、全角を同値とする( NLSSORT と JAPANESE_M_CI ) Oracle 10g

Oracle 10g から 'NLS_SORT=JAPANESE_M_CI' と CI(Case Ignore) キーワードが追加されている。
CI を追加すると大文字、小文字、全角、半角において、同じコードポイントを戻す。

NLSSORT('a','NLS_SORT=JAPANESE_M_CI')
NLSSORT('A','NLS_SORT=JAPANESE_M_CI')
NLSSORT('A','NLS_SORT=JAPANESE_M_CI')

の各値が同一値であることを示し、つまりコードポイントの違いを意識せずに取り扱えることをあらわす。

同様に半角と全角カタカナの'ア'と'あ'を同値として検索することも可能になる。

-- 以下の等価条件も TRUE になる。
NLSSORT('ア','NLS_SORT=JAPANESE_M_CI')
NLSSORT('ア','NLS_SORT=JAPANESE_M_CI')
NLSSORT('あ','NLS_SORT=JAPANESE_M_CI')

Oracle 9i(R2) の場合には、過渡的?(部分的)な対応がされている(NLS_SORT=GENERIC_BASELETTER)。
これは言語の区別がされていないために恩恵が少なく将来性も心配である、日本語環境に用いるのは控えた方がよいかもしれない。 



関連事項

関連 SQL 関数

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ 会員制(無料)の公式技術サイト