Oracle® 非公式 SQL, PL/SQL & DBA's ガイドサイト

大文字、小文字や全角、半角を区別しないで検索する (NLSSORT 関数)

大文字、小文字だけの区別であれば UPPER や LOWER、全角、半角だけの区別であれば TO_MULTI_BYTE や TO_SINGLE_BYTE を使用することで検索することができる。
しかし、NLSSORT 関数を使用することによって、より見やすく簡単に実現できる。

各言語サポートソート (NLSSORT) 関数は検索条件に使用することで辞書ワード検索に使用することが可能である。

要注意:但し、10g R1 時点において大文字、小文字や全角、半角に関する区別はおおよそ吸収されるものの、全角カナと半角カナの差異は完全には吸収されていない(半濁点、濁点を含むカタカナ)ので注意する(これが正しい仕様ではない気がする)。
また NLS_COMP と NLS_SORT の NLS パラメータ を設定することによってオプティマイザによるアクセスパスがパフォーマンスに影響が出るくらいに大きく変化することにも注意を払う必要がある。

NLS_SORT パラメータと NLS_COMP パラメータ

• BINARY、BINARY の組み合わせ

NLS_COMP='binary'
NLS_SORT='BINARY' の場合 (デフォルト)

--
SELECT ALPHA_TEXT FROM ALPHA_TBL WHERE ALPHA_TEXT = 'a';
 
ALPHA_TEXT
----------------------------------------
a

• ANSI と JAPANESE_M_CI (JAPANESE_M) の組み合わせ

NLS_COMP='ansi'
NLS_SORT='JAPANESE_M_CI' の場合

ALTER SESSION SET NLS_COMP='ansi';
-- JAPANESE_M_CI ( Oracle 10g 〜)
ALTER SESSION SET NLS_SORT='JAPANESE_M_CI';
--
SELECT ALPHA_TEXT FROM ALPHA_TBL WHERE ALPHA_TEXT = 'a';
 
ALPHA_TEXT
----------------------------------------
a
A
ａ
Ａ

NLSSORT 関数と NLS_SORT パラメータと NLS_COMP パラメータ

上記の SQL は以下の文と同じ意味になる。

ALTER SESSION SET NLS_COMP='binary';
ALTER SESSION SET NLS_SORT='BINARY';
--
SELECT
	ALPHA_TEXT
FROM ALPHA_TBL
WHERE
	NLSSORT(ALPHA_TEXT,'NLS_SORT=JAPANESE_M_CI')=NLSSORT('a','NLS_SORT=JAPANESE_M_CI');
 
ALPHA_TEXT
----------------------------------------
a
A
ａ
Ａ

参考 ⇒ NLSSORT 関数

テストデータの構築スクリプト

CREATE TABLE RIVUS.ALPHA_TBL
(
	ALPHA_TEXT	VARCHAR2(20)
);
-- 半角 A
INSERT INTO ALPHA_TBL VALUES('a');
INSERT INTO ALPHA_TBL VALUES('A');
-- 全角 Ａ
INSERT INTO ALPHA_TBL VALUES('ａ');
INSERT INTO ALPHA_TBL VALUES('Ａ');
COMMIT;

関連事項

• SELECT
• 検索結果を辞書順に取り出す
• NLS 関連パラメータ
• 半角カタカナを全角に変換する方法