Oracle SQL Order by, sequence of numbers and characters

this posting explains how to control the ordering in which characters and numbers are returned when a order by clause is used. to keep it simple i do not explain how to arrange this without a order by clause and an index organized table. if the order by clause is used without the nlssort function then results may vary from time to time. this is because of some session scoped parameter values affect the sequence of the result. These are NLS_SORT and NLS_LANGUAGE. if NLS_SORT is empty, its value is derived from NLS_LANGUAGE / NLS_LANG.

Often NLS_SORT defaults to BINARY. This is the case when NLS_LANGUAGE is set to AMERICAN. The binary sort method sorts by byte value. On ASCII based platforms this results in the sequence: numbers,uppercase chars,lowercase chars. on the other side it is possible to set a specific value eg. GERMAN. See example:



SQL> alter session set NLS_SORT=BINARY;

SQL> select * from ( select 'r' as dummy from dual union select '9' from dual union select 'R' from dual ) order by 1;

D
-
9
R
r

SQL> alter session set NLS_SORT=GERMAN;

SQL> select * from ( select 'r' as dummy from dual union select '9' from dual union select 'R' from dual ) order by 1;

D
-
r
R
9



hardcoding is an approach to be independent of NLS parameters. This is done with the help of the nlssort function. See example:



SQL> select * from ( select 'r' as dummy from dual union select '9' from dual
union select 'R' from dual ) order by nlssort(dummy,'NLS_SORT = BINARY ');

D
-
9
R
r

SQL> select * from ( select 'r' as dummy from dual union select '9' from dual
union select 'R' from dual ) order by nlssort(dummy,'NLS_SORT = GERMAN ');

D
-
r
R
9



Note that you have to use the column name within the nlssort function, dont use nlssort(1, ...) as the function uses 1 as a constant and not the first column.

For most counties the derived NLS_SORT setting produces a "AaBbCc ... 0123456789" sorting sequence by default. but take care when using AMERICAN, Arabic, Icelandic, Polish settings; this results in "0123456789 AaBbCcDd ..."

to get the relevant NLS parameter values for your DB you can query the view DATABASE_PROPERIES, to get the values in the session scope use NLS_SESSION_PARAMETERS.