how to sort by case insensitive alphabetical order using COLLATE NOCASE

Question

I am trying to sort alphabetically case insensitive using COLLATE NOCASE but getting error

ORA - 00933 SQL command not properly ended.

below is the query I am firing:

SELECT LPN.LPN_ID, 
       LPN.TC_ORDER_ID, 
       ORDERS.D_NAME, 
       ORDERS.PURCHASE_ORDER_NUMBER AS ORDER_PURCHASE_ORDER_NUMBER, 
       ORDERS.D_NAME AS D_NAME_2, LPN.LPN_NBR_X_OF_Y 
  FROM ORDERS ORDERS, 
       LPN LPN 
 WHERE ORDERS.ORDER_ID=LPN.ORDER_ID 
 ORDER BY ORDERS.D_NAME COLLATE NOCASE DESC

I checked here to try this but still getting error How to use SQL Order By statement to sort results case insensitive? Any suggestions please ?

Answer 1

Oracle does not support COLLATE NOCASE option of the order by clause. To be able to perform case-insensitive ordering you have two options:

1. Set NLS_COMP='ANSI' and 'NLS_SORT=BINARY_CI', CI suffix means case-insensitive, session or system wide by using alter session or alter system statement:

   alter session set nls_comp='ANSI';
   alter session set nls_sort='BINARY_CI';
   with t1(col) as(
    select 'A' from dual union all
    select 'a' from dual union all
    select 'b' from dual union all
    select 'B' from dual
   )
   select *
     from t1
    order by col
   

   Result:

   COL
   ---
   A
   a
   b
   B
   

2. Change case of the character literal by using either upper() or lower() function.

     with t1(col) as(
       select 'A' from dual union all
       select 'a' from dual union all
       select 'b' from dual union all
       select 'B' from dual
     )
     select *
       from t1
      order by upper(col)
   

   result:

   COL
   ---
    A
    a
    b
    B
   

---

Edit

but i need the UpperCase to preceed any LowerCase eg. Alan, alan, Brian, brian, Cris

This is not the case-insensitive ordering, rather quite contrary in some sense. As one of the options you could do the following to produce desired result:

with t1(col) as(
   select 'alan' from dual union all
   select 'Alan' from dual union all
   select 'brian' from dual union all
   select 'Brian' from dual union all
   select 'Cris' from dual
 )
 select col
   from ( select col
               , case
                   when row_number() over(partition by lower(col) 
                                              order by col) = 1
                   then 1
                   else 0
                 end as rn_grp
           from t1
         )
  order by sum(rn_grp) over(order by lower(col))

Result:

COL
-----
Alan
alan
Brian
brian
Cris

Answer 2

COLLATE NOCASE does not work with Oracle, Try this:

SELECT LPN.LPN_ID,
     LPN.TC_ORDER_ID,
     ORDERS.D_NAME,
     ORDERS.PURCHASE_ORDER_NUMBER AS ORDER_PURCHASE_ORDER_NUMBER,
     ORDERS.D_NAME AS D_NAME_2,
     LPN.LPN_NBR_X_OF_Y
FROM orders orders,
     lpn lpn
where orders.order_id=lpn.order_id
ORDER BY lower(orders.d_name) DESC;