如何在Oracle中像EXCEL一样对带有'_'的文本进行排序? [英] How to sort texts with '_' in Oracle exactly like EXCEL?

问题描述

在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_LANGUAGE派生的默认值.

NLS_SORT specifies the type of sort for character data. It overrides the default value that is derived from NLS_LANGUAGE.

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一样对带有'_'的文本进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！