如何使用 COLLATE NOCASE 按不区分大小写的字母顺序排序

Question

我正在尝试使用COLLATE NOCASE进行不区分大小写的字母顺序排序，但是出现了错误。

ORA - 00933 SQL命令未正确结束。

以下是我正在执行的查询：

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

我在这里检查了一下，尝试了这个方法，但仍然出现错误：如何使用SQL Order By语句对结果进行不区分大小写的排序？有什么建议吗？

- maddy

Answer 1

Oracle不支持COLLATE NOCASE选项，这个选项用于order by子句。若要实现不区分大小写的排序，您有两个选项：

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
   

---

编辑

但我需要大写字母在小写字母之前，例如：Alan、alan、Brian、brian、Cris。

这不是大小写不敏感的排序，某种意义上相反。作为其中的一个选项，您可以执行以下操作以产生所需结果：

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))

结果：

COL
-----
Alan
alan
Brian
brian
Cris

- Nick Krasnov

---

我使用了您提供的第二个选项 lower().. 这很好用 但我需要大写字母在小写字母之前出现，例如：Alan, alan, Brian, brian, Cris. - maddy

Answer 2

COLLATE NOCASE 在 Oracle 中不起作用，尝试使用以下方法：

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;

- Rajiv Ranjan

---

ORDER BY lower/upper(orders.d_name) DESC 就可以解决问题。 - Vishrant

Answer 3

自从10g以来，有一个名为NLSSORT的函数，它可以做类似于Nicholas Krasnov描述的事情，但不需要更改系统或会话。

因此，您可以尝试类似于以下内容：

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

请注意，您不能直接在 UNION 中使用此功能，否则您将收到以下错误信息：

ORA-01785: ORDER BY 项目必须是 SELECT 列表表达式的数字。

相反，您需要进行包装处理：

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

- A.B.Cade

---

在Oracle 12c中，我认为您只需在ORDER BY子句之后添加“collate binary_ci”即可。 - Steve Lloyd