Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

Oracle10g Case-Insensitive and Accent-Insensitive Query and Sort

You use the nls_sort session parameter to specify a case-insensitive or accent-insensitive sort, this is accomplished by:

• Appending a " _CI" to an Oracle sort name for a case-insensitive sort.
• Appending an "_AI" to an Oracle sort name for an accent-insensitive and case-insensitive sort.

For example, you can set nls_sort to the following types of values:

• SPANISH becomes SPANISH_AI
• DUTCH becomes DUTCH_CI

Binary sorts can also be case-insensitive or accent-insensitive. When you specify binary_ci as a value for nls_sort, it designates a sort that is accent-sensitive and case-insensitive. binary_ai designates an accent-insensitive and case-insensitive binary sort. You may want to use a binary sort if the binary sort order of the character set is appropriate for the character set you are using.

For example, with the nls_lang environment variable set to american_america.we8iso8859p1, to use an example right out of the Oracle Database 10g manual, you can create a table called test1 and populate it as follows:

SQL> CREATE TABLE test1 (letter VARCHAR2(10));
SQL> INSERT INTO test1 VALUES(’ä’);
SQL> INSERT INTO test1 VALUES(’a’);
SQL> INSERT INTO test1 VALUES(’A’);
SQL> INSERT INTO test1 VALUES(’Z’);
SQL> SELECT * FROM test1;

LETTER
------
ä
a
A
Z

Since the default value of nls_sort is BINARY you don't need to specify anything extra to use a binary sort. Use the following statement to do a binary sort of the characters in table test1:

SELECT * FROM test1 ORDER BY letter;

To change the value of nls_sort, you would enter a statement like (only using your sort specifier):

ALTER SESSION SET NLS_SORT=BINARY_AI;

When nls_sort=binary, uppercase letters come before lowercase letters. Letters with diacritics appear last. If the sort considers diacritics but ignores case (binary_ci), the letters with diacritics will appear last. When both case and diacritics are ignored (binary_ai), the "ä" is sorted with the other characters whose base letter is "a". All characters whose base letter is "a" occur before any occurrence of the letter "z". You should use binary sorts for better performance when the character set is US7ASCII, or any other character set that has the same sort order as the binary sorts.

The following table shows the results from setting nls_sort to binary, binary_ci, and binary_ai.

The next table shows the sort orders that result from German sorts for the table. A German sort places lowercase letters before uppercase letters, and a "ä" occurs before a "Z". When the sort ignores both case and diacritics (using nls_sort=german_ai), the "ä" appears with the other characters whose base letter is an "a".

The next feature we will discuss is the enhanced CONNECT BY support.