Get SQL Server 2005 sort varchar columns just as Java would do

Question

My problem is very simple: I have an array of String. Sorting it in Java gives one order , and in Sql Server 2005 slightly different order . An example of difference I spotted was in the case of two strings: "jyl ; pagespro" , "jyl" , which Java sorts in this order, and Sql in the inverse order . I tried to make Sql Server order by ascii(myColumn) but still with no effect. How can I solve this ??.....

Answer 1

To extend on DaveE's answer, if you have determined the collation you want to use for sorting, you can use its name in the ORDER BY clause like this: ORDER BY colA COLLATE SQL_Latin1_General_Cp437_CS_AS_KI_WI.

Please be aware that this would not use an index for sorting that might be defined on colA. You could get around that by using calculated columns.

In general, I would suggest to do all sorting in one place: either in Java, or in the database, but not sometimes here and sometimes there. This just leads to confusion and complexity.

Answer 2

I'd suspect it has to do with the SQL Server collation in effect. That affects not only the available character set but also how the characters sort against one another. Even in the fairly generic SQL_Latin1_General family, there are dozens of specific collations available.