0

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

But, In Oracle, when I sort in ascending order, results shows as below. (I guess, Oracle treats '_' < 'A' False)

C10A
C10_
C20A
C20_

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.

My sorting code is as below,

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;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
Soon
  • 491
  • 3
  • 16

1 Answers1

1

You can achieve this by altering the sorting method using NLS_SORT as following:

According to ORACLE Documentation:

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

NLS_SORT contains either of the following values:

NLS_SORT = BINARY | sort_name

BINARY specifies a binary sort. sort_name specifies a linguistic sort sequence.

Here is how you can achieve the result.

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>

Cheers!!

Popeye
  • 35,427
  • 4
  • 10
  • 31