Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
Accent-Insensitive Query and Sort

Oracle Tips by Burleson


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.

BINARY

BINARY_CI

BINARY_AI

A

a

Z

A

a

a

Z

A

Z

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".

GERMAN

GERMAN_CI

GERMAN_AI

a

a

A

A

a

A

Z

Z

Z

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

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright 1996 -2016 by Burleson. All rights reserved.


Oracle is the registered trademark of Oracle Corporation. SQL Server is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks