Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Wang.

Asked: January 12, 2017 - 3:41 am UTC

Last updated: January 20, 2017 - 10:50 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

According to Oracle document, the sort order depends on database option NLS_SORT, and there are only 2 kinds of sort: Binary Sort and Linguistic Sort. Is there any other options to change the sort order?

Currently, the following sql will always return the same order either using Binary Sort or Linguistic Sort:
select 'F883967,' from dual
union
select 'F883967_1,' from dual
union
select 'F883967' from dual
order by 1;


Here is the result set returned:
F883967
F883967,
F883967_1,

Is it possible to change the sort order to get following order(Since the Sybase SQL anywhere SORT_COLLATION=1252LATIN1, it will return following order, we need to keep the same order after the migration)?
F883967
F883967_1,
F883967,

BTW, I know we can specify custom order by in the sql, but I don't want to do that because we have a lot of sqls need to be changed. I would like to know if we can have some parameter in DB side which don't need to touch the sql to resolve this issue.? Thanks

and Chris said...

So you want commas to sort after underscores?

I reached out to Sergiusz Wolicki, our NLS expert on this one. He had this to say:

"It looks like Sybase collations (since Sybase Anywhere 10) are based on the Unicode Collation Algorithm and this is what makes "low line" (aka "underscore") to come before "comma". You can get this order in Oracle Database 12.1.0.2 by using NLS_SORT=UCA0620_DUCET. (In Oracle Database 12.1.0.1, NLS_SORT=UCA0610_DUCET_S4_VS_BN_NY_EN_FN_HN_DN_MN should work as well but 12.1.0.2 is much preferred anyway). In Oracle Database 12.2, we recommend NLS_SORT=UCA0700_ORADUCET instead, which is a newer version and behaves better when max_string_size=extended is set. UCA collations are a bit heavy but if they just use them to sort small result sets, then this should not matter much. There is a theoretical possibility to define a custom monolingual collation using Oracle Locale Builder, but this creates a maintenance nightmare for the customer and we strongly discourage it."

So if you use one of the collations he specifies above or equivalent, you should get the output you want:

SQL> select * from nls_session_parameters
  2  where  parameter = 'NLS_SORT';

PARAMETER                      VALUE
------------------------------ ----------------------
NLS_SORT                       BINARY

SQL>
SQL> select * from (
  2  select ',' x, 1 y from dual
  3  union all
  4  select '_' x, 2 y from dual
  5  )
  6  order by x;

X          Y
- ----------
,          1
_          2

SQL>
SQL> alter session set NLS_SORT=UCA0620_DUCET;

Session altered.

SQL>
SQL> select * from (
  2  select ',' x, 1 y from dual
  3  union all
  4  select '_' x, 2 y from dual
  5  )
  6  order by x;

X          Y
- ----------
_          2
,          1

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 16, 2017 - 10:11 am UTC

I have tried to set NLS_SORT to "UCA0620_DUCET", and it works as expected when I use following sql:

select 'F883967,' from dual
union
select 'F883967_' from dual
order by 1

it returns:
F883967_
F883967,

but I am surprised when I use following sql:
select 'F883967,' from dual
union
select 'F883967_1' from dual
order by 1

it returns:
F883967,
F883967_1

But in Sybase, it returns:
F883967_1
F883967,

Can you expain it?






Chris Saxon
January 20, 2017 - 10:50 am UTC

It appears to be to do with the extra length of the string with _1:

alter session set NLS_SORT = "UCA0620_DUCET";
select 'F883967,' from dual
union
select 'F883967_' from dual
order by 1 ;

'F883967,'  
F883967_    
F883967, 

select 'F883967,' from dual
union
select 'F883967_1' from dual
order by 1 ;

'F883967,'  
F883967,    
F883967_1   

select 'F883967,1' from dual
union
select 'F883967_1' from dual
order by 1 ;

'F883967,1'  
F883967_1    
F883967,1


I'm not entirely sure why. Can't you just update your code? ;)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.