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
how to sort by case insensitive alphabetical order using COLLATE NOCASE
提问by maddy
I am trying to sort alphabetically case insensitive using COLLATE NOCASE
but 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 NOCASE
option of the order by
clause. To be able to perform case-insensitive ordering you have two options:
Oracle 不支持子句的COLLATE NOCASE
选项order by
。为了能够执行不区分大小写的排序,您有两个选择:
Set
NLS_COMP='ANSI'
and'NLS_SORT=BINARY_CI'
,CI
suffix means case-insensitive, session or system wide by usingalter session
oralter 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
Change case of the character literal by using either
upper()
orlower()
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
设置
NLS_COMP='ANSI'
和'NLS_SORT=BINARY_CI'
,CI
后缀表示不区分大小写,会话或系统范围使用alter session
oralter 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
使用
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 NOCASE
does 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