All Forums Database
nileshsane 18 posts Joined 10/07
05 Mar 2008
Sorting diffenceces - Order by

I have the following delimmacreate 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 1In SQL Server 2005 the above select returns_a _a _a_b _b _ba_ a_ a_In Teradata the above select returns a_ a_ a_ _a _a _a _b _b _bClearly 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.

Fred 1096 posts Joined 08/04
05 Mar 2008

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;

nileshsane 18 posts Joined 10/07
10 Mar 2008

Thanks for the reply Fred.

You must sign in to leave a comment.