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

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/19375330/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 02:00:28  来源:igfitidea点击:

how to sort by case insensitive alphabetical order using COLLATE NOCASE

sqloraclesorting

提问by maddy

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

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

ORA - 00933 SQL command not properly ended.

ORA - 00933 SQL 命令没有正确结束。

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 ?

我在这里检查以尝试此操作,但仍然出现错误 如何使用 SQL Order By 语句对不区分大小写的结果进行排序?请问有什么建议吗?

回答by Nick Krasnov

Oracle does not support COLLATE NOCASEoption of the order byclause. To be able to perform case-insensitive ordering you have two options:

Oracle 不支持子句的COLLATE NOCASE选项order by。为了能够执行不区分大小写的排序,您有两个选择:

  1. Set NLS_COMP='ANSI'and 'NLS_SORT=BINARY_CI', CIsuffix means case-insensitive, session or system wide by using alter sessionor alter systemstatement:

    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
    
  1. 设置NLS_COMP='ANSI''NLS_SORT=BINARY_CI'CI后缀表示不区分大小写,会话或系统范围使用alter sessionoralter system语句:

    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
    

    结果:

    COL
    ---
    A
    a
    b
    B
    
  2. 使用upper()lower()函数更改字符文字的大小写。

      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)
    

    结果:

    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

回答by Rajiv Ranjan

COLLATE NOCASEdoes not work with Oracle, Try this:

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;

回答by A.B.Cade

Since 10g there is a function NLSSORTwhich does pretty much what Nicholas Krasnov describedbut doesn't require altering the system or session.

从 10g 开始,有一个函数NLSSORT 几乎可以完成Nicholas Krasnov描述的功能,但不需要更改系统或会话。

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:

请注意,您不能直接在 UNION 中使用它,否则会出现以下错误:

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

ORA-01785:ORDER BY 项必须是 SELECT 列表表达式的编号。

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