Sorting diffenceces - Order by

Database
Enthusiast

Sorting diffenceces - Order by

I have the following delimma
create table a (a varchar(100),aa varchar(100),aaa varchar(100))

insert into a values ('a_','a_','a_');
insert into a values ('_b','_b','_b');
insert into a values ('_a','_a','_a');

select * from a order by 1

In SQL Server 2005 the above select returns
_a _a _a
_b _b _b
a_ a_ a_

In Teradata the above select returns
a_ a_ a_
_a _a _a
_b _b _b

Clearly the underscore character is represented differently in both the databases.
I am using Code Page 1, Case Insensitive , Ascending Sort collation in SQL Server. I need to use the UNICODE character set in Teradata, but what about the collation? Which one will provide me with the default sort which is similar to the one which SQL Server is returning.
2 REPLIES
Teradata Employee

Re: Sorting diffenceces - Order by

The default Teradata MULTINATIONAL collation sequence may do what you want. I don't think it matches SQL Server 100%, but it should sort underscores before letters. If all else fails, and this is an absolute requirement, it's possible for your system administrator to install a user-defined collation sequence.

To change collation for the current session:
SET SESSION COLLATION MULTINATIONAL;

To change default collation for future sessions for a user:
MODIFY USER myusername AS COLLATION = MULTINATIONAL;
Enthusiast

Re: Sorting diffenceces - Order by

Thanks for the reply Fred.