问题描述
在Excel中,当我按升序对文本进行排序时,结果如下所示.带下划线字符的文本位于其他字符之前.在Excel单元格中,当我键入="_"<按"A",然后按预期显示"True".
In Excel When I sort the texts in ascending order, results shows as below. Text with the underscore character precedes the others. And in Excel cell, when I type in ="_" < "A", then "True" shows as expected.
C10_
C10A
C20_
C20A
但是,在Oracle中,当我按升序排序时,结果显示如下.(我想,Oracle对待'_'<'A'False)
But, In Oracle, when I sort in ascending order, results shows as below.(I guess, Oracle treats '_' < 'A' False)
C10A
C10_
C20A
C20_
如何使Oracle与Excel完全一样对列表进行排序?我已经将ASC更改为DESC,但是结果却不是我期望的.
How can I make Oracle sort the list exactly as Excel does? I have changed ASC to DESC, but the result was not what I expect.
我的排序代码如下,
WITH DATAA AS (
SELECT *
FROM
(
SELECT 'C10_'rr FROM DUAL
UNION
SELECT 'C10A' rr FROM DUAL
UNION
SELECT 'C20_' rr FROM DUAL
UNION
SELECT 'C20A' rr FROM DUAL
)
)
SELECT *
FROM DATAA
ORDER BY rr ASC;
推荐答案
您可以通过使用 NLS_SORT
更改排序方法来实现此目的,如下所示:
You can achieve this by altering the sorting method using NLS_SORT
as following:
根据ORACLE文档:
According to ORACLE Documentation:
NLS_SORT包含以下值之一:
NLS_SORT contains either of the following values:
NLS_SORT =二进制|排序名称
NLS_SORT = BINARY | sort_name
BINARY指定二进制排序.sort_name指定语言排序顺序.
BINARY specifies a binary sort. sort_name specifies a linguistic sort sequence.
这是获得结果的方法.
SQL> -- Your original query
SQL> --
SQL> WITH DATAA AS (
2 SELECT *
3 FROM
4 (
5 SELECT 'C10_'rr FROM DUAL UNION
6 SELECT 'C10A' rr FROM DUAL UNION
7 SELECT 'C20_' rr FROM DUAL UNION
8 SELECT 'C20A' rr FROM DUAL )
9 )
10 SELECT *
11 FROM DATAA
12 ORDER BY rr ASC;
RR
----
C10A
C10_
C20A
C20_
-
SQL> -- Now altering the sorting method
SQL> --
SQL> --
SQL> alter session set NLS_SORT = German;
Session altered.
SQL> --
SQL> --
SQL> -- Now see the result
SQL> --
SQL> --
SQL> WITH DATAA AS (
2 SELECT *
3 FROM
4 (
5 SELECT 'C10_'rr FROM DUAL UNION
6 SELECT 'C10A' rr FROM DUAL UNION
7 SELECT 'C20_' rr FROM DUAL UNION
8 SELECT 'C20A' rr FROM DUAL )
9 )
10 SELECT *
11 FROM DATAA
12 ORDER BY rr ASC;
RR
----
C10_
C10A
C20_
C20A
SQL>
干杯!
这篇关于如何在Oracle中像EXCEL一样对带有'_'的文本进行排序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!