Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

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')

全角、半角、濁点、半濁点を考慮したい場合 ( 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 から 'NLS_SORT=JAPANESE_M_CI' と CI(Case Ignore) キーワードが追加されている。
CI を追加すると大文字、小文字、全角、半角において、同じコードポイントを戻す。

NLSSORT('a','NLS_SORT=JAPANESE_M_CI')
NLSSORT('A','NLS_SORT=JAPANESE_M_CI')
NLSSORT('Ａ','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)。
これは言語の区別がされていないために恩恵が少なく将来性も心配である、日本語環境に用いるのは控えた方がよいかもしれない。　

---

関連事項

• NLS 関連パラメータ
• 全角・半角、大文字・小文字を区別しない検索
• 検索結果を辞書順に取り出す

関連 SQL 関数

• 文字コードを変換する ⇒ convert ( e, d, s )
• 文字列を文字単位に置換する ⇒ translate ( e, f, t )
• 文字列を単語レベルで置換する ⇒ replace ( s1, s2, r )
• 文字列を反転・逆さ読みする ⇒ reverse_multi ( s )
• 英文字の全てを小文字に変換する ⇒ lower ( s )
• 半角カナを全角かなに変換する ⇒ to_full_kana ( s, t )