ORACLE Sort Alphabetically Upper A, Lower a, numerics

Question

I am sorting with Oracle version 12 with the following NLS parameters

NLS_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_COMP BINARY

Eg. " 1Andy"," Andy","Andy","andy","Aaron","Bob"

SELECT name from employee order by name

result: " 1Andy"," Andy","Aaron","Andy","Bob","andy"

if I change the NLS_SORT to WEST_EUROPEAN, it is suppose to be " 1Andy"," Andy","Aaron","Andy","andy","Bob" but the result is " 1Andy"," Andy","andy","Aaron","Andy","Bob" where andy even though is lower case goes in the middle.

SELECT name from employee order by NLSSORT(name ,'NLS_SORT=WEST_EUROPEAN')

enter image description here

gsalem gsalem · Accepted Answer · 2021-03-23T09:34:36

To begin with, you can have case-insensitive sorting by adding _ci the the collation spec: west_european_ci. That being said, it seems in west_european sorting spec, number come after alpha chars. Here's what I get with your data using different collation specs

SQL> with dt as (
  2  select ' 1Andy' cv from dual
  3  union all
  4  select ' Andy' from dual
  5  union all
  6  select '1Andy' from dual
  7  union all
  8  select 'Andy' from dual
  9  union all
 10  select 'andy' from dual
 11  union all
 12  select 'Aaron' from dual
 13  union all
 14  select 'Bob' from dual)
 15  select *
 16  from dt
 17  order by cv;

CV    
------
 1Andy
 Andy
1Andy
Aaron
Andy
Bob
andy

7 rows selected. 

SQL> 
SQL> with dt as (
  2  select ' 1Andy' cv from dual
  3  union all
  4  select ' Andy' from dual
  5  union all
  6  select '1Andy' from dual
  7  union all
  8  select 'Andy' from dual
  9  union all
 10  select 'andy' from dual
 11  union all
 12  select 'Aaron' from dual
 13  union all
 14  select 'Bob' from dual)
 15  select *
 16  from dt
 17  order by nlssort(cv,'NLS_SORT=WEST_EUROPEAN');

CV    
------
 Andy
 1Andy
Aaron
Andy
andy
Bob
1Andy

7 rows selected. 

SQL> 
SQL> with dt as (
  2  select ' 1Andy' cv from dual
  3  union all
  4  select ' Andy' from dual
  5  union all
  6  select '1Andy' from dual
  7  union all
  8  select 'Andy' from dual
  9  union all
 10  select 'andy' from dual
 11  union all
 12  select 'Aaron' from dual
 13  union all
 14  select 'Bob' from dual)
 15  select *
 16  from dt
 17  order by nlssort(cv,'NLS_SORT=WEST_EUROPEAN_ci');

CV    
------
 Andy
 1Andy
Aaron
Andy
andy
Bob
1Andy

7 rows selected.

ORACLE Sort Alphabetically Upper A, Lower a, numerics

1 Answers