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;

Answer 3

Since 10g there is a function NLSSORT which does pretty much what Nicholas Krasnov described but doesn't require altering the system or session.

so you can try something like 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 nlssort(ORDERS.D_NAME, 'NLS_SORT = binary_ci') desc

Note you can't use this directly in a UNION or you'll get the following error:

ORA-01785: ORDER BY item must be the number of a SELECT-list expression.

Instead, you need to wrap it:

SELECT * FROM (SELECT a, b FROM x, y UNION SELECT c, d FROM m, n)
ORDER BY nlssort(a, 'nls_sort=binary_ci') DESC