How can I sort by a table column in varying cases (Oracle)

Question

How can I sort a table with a column of varchar2 with characters in varying cases (UPPER and lower)?

For example, when I do an order by of the Name column, I get the following results:

ANNIE
BOB
Daniel
annie
bob

What I want is something like this:

ANNIE
annie
BOB
bob
Daniel

Solution 1

Use lower(field), e.g.

select * from tbl order by lower(name)

If you need to address special characters for non-english languages then the other answers about NLSSORT may be what you need. If you don't I would try and KISS and use lower() as it is very easy to remember and use and be read by others (maintainability).

Solution 2

Another option is the use of the NLSSORT function to perform linguistic sorting:

SQL> with test as (select 'ANNIE' as col from dual
  2      union all select 'BOB' from dual
  3      union all select 'Daniel' from dual
  4      union all select 'annie' from dual
  5      union all select 'bob' from dual
  6      union all select 'Ångström' from dual
  7      union all select 'ångström' from dual)
  8  select col
  9  from test
 10  order by nlssort(col, 'NLS_SORT = WEST_EUROPEAN')
 11  /

COL
----------
Ångström
ångström
ANNIE
annie
BOB
bob
Daniel

The advantages are more flexibility. One can sort characters with accents as well as different cases together. One can choose to treat some characters in a language specific way by specifying different values for NLS_SORT. Defines an order within the set of equivalent characters. So 'A' and 'a' are sorted together, but within the 'a's, the upper case comes first. Disadvantages I expect that NLSSORT uses more CPU than LOWER, though I have not bench marked it. And NLSSORT will only use a prefix of longer strings:

The string returned, also known as the collation key, is of RAW data type. The length of the collation key resulting from a given char value for a given collation may exceed 2000 bytes, which is the maximum length of the RAW value returned by NLSSORT. In this case, NLSSORT calculates the collation key for a maximum prefix, or initial substring, of char so that the calculated result does not exceed 2000 bytes. For monolingual collations, for example FRENCH, the prefix length is typically 1000 characters. For multilingual collations, for example GENERIC_M, the prefix is typically 500 characters. The exact length may be lower or higher depending on the collation and the characters contained in char.

Solution 3

If you're on relatively recent versions of Oracle, you should look at setting NLS_SORT/NLS_COMP, rather than using the LOWER() function.

If you don't want to globally affect the instance, you can use the NLSSORT() function to set the NLS_SORT for the scope of a specific query.

SQL> create table case_insensitive(a varchar2(10));

Table created.

SQL> insert into case_insensitive values('D');

1 row created.

SQL> 
SQL> 
SQL> c/'D/'c
  1* insert into case_insensitive values('c')
SQL> /

1 row created.

SQL> c/'c/'B
  1* insert into case_insensitive values('B')
SQL> /

1 row created.

SQL> c/'B/'a
  1* insert into case_insensitive values('a')
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from case_insensitive;

A
----------
D
c
B
a

SQL> select * from case_insensitive order by a;

A
----------
B
D
a
c

SQL> select * from case_insensitive order by nlssort(a,'NLS_SORT=BINARY_CI'); 

A
----------
a
B
c
D

A good example of this can be found here.

Comments

[beny23]

One thing to add would be that this would mean that unless there is a functional index on lower(name) a full table scan would be done with this query.

[DCookie]

This query will always require a full table scan, with or without an ORDER BY. Usually the issue you're describing is more important with the WHERE clause.

[Shannon Severance]

Setting NLS_SORT/NLS_COMP will change the behavior of all queries. Which is great if that's what one wants. Not so great if it isn't.

[Shannon Severance]

See docs.oracle.com/cd/E11882_01/server.112/e10729/… for information on using indexes with linguistic sorting.

[Mark J. Bobak]

Not necessarily. I'll go back and edit my example to demonstrate.

[Shannon Severance]

NLSSORT & NLS_SORT are both defined by Oracle but are different things. The top of this answer referenced NLS_SORT, a parameter. However the code added after I commented uses the NLSSORT function. So if you originally meant NLSSORT, you are correct that my comment would not apply. NLSSORT docs.oracle.com/cd/E11882_01/server.112/e26088/…, NLS_SORT docs.oracle.com/cd/E11882_01/server.112/e25513/…

[Jeffrey Kemp]

@DCookie: do you want to bet? ;) e.g. try a functional index on lower(name) appended with all the columns from the table = full index scan, plus no sort :) - just sayin'

[Mark J. Bobak]

The article I linked to covered both. If you don't like my answer, you're free to vote it down.

[durette]

Not to be pedantic, but UPPER() would be the canonical, textbook way to do this. If a function-based index exists or will exist, you're more likely to find it on UPPER() than LOWER().