Character Sort in Oracle

Character sorting in Oracle is actually case sensitive, means "A" is different from "a".
For example, if one of my table (named test_table), have a column (named col1) with character data as below :

Col1
-------
A
B
C
d
e
a
b
E
D
c

Now if we execute below statement :

SELECT col1
FROM test_table
ORDER BY col1;

The output will be like :

Col1
-----
A
B
C
D
E
a
b
c
d
e

This is because the sort in Oracle is case sensitive and by default Oracle sorts using a binary sort order, so all upper case letters come before the lower case letters. This means that in example like this one, text is sorted according to the numeric equivalent. But most of time the user's requirement is to fetch the data sorted irrespective of case, means we require case insensitive sort. Most people expect to see A and a stored together, regardless of cases. To achieve this, we use NLS directive as :

ALTER SESSION SET NLS_SORT='BINARY_CI';

Now after execution of above statement, if we execute the same statement, the output will be the desired (case insensitive) :

SELECT col1
FROM test_table
ORDER BY col1;

The output will be like :

Col1
-----
A
a
B
b
C
c
D
d
E
e

Actually Oracle supports both case-sensitive and case-insensitive sorts, and by default apply the case-sensitive sort also known as BINARY. For every such sort order, an equivalent insensitive order exists using a suffix of _CI. We changed to using the BINARY_CI sort order.

One another approach to do the same is you can change the whole column in where clause to either upper or lower case like :

SELECT col1
FROM test_table
ORDER BY upper(col1);

The output will be like :

Col1
-----
A
a
B
b
C
c
D
d
E
e

But it has the disadvantage of preventing the optimizer from using standard indexes, and you need to create create function-based indexes to counter this.