SQLTeam.com | Weblogs | Forums

What Collation to Use?


#1

I would like to use a collation that will place characters from Char(123) to Char(255) higher in the sorting sequence. For example, currently the default collation will sort A, 7, C and ~ (tilde = Char(126)) as follows:

~
7
A
C

I want it to sort in the following way, which is consistent with the ASCII character set sorting sequence:

7
A
C
~

What is the collation that I should use to achieve this?

Thanks!


#2

As you know that the SQL Server supports multiple collations in a single database to store the objects that have. In SQL Server for a non-Unicode columns, the code page is specified for collation setting or the data and which the characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page.
Please go to this article here it may be helpful for you. https://msdn.microsoft.com/en-us/library/ms143726.aspx#Collation_Defn


#3

Thank you so much Jason! Based on the article you linked to, I find that Latin1_General_BIN is the collation that I need:

select 'A' collate Latin1_General_BIN as X union select 'C' union select '7' union select '~' order by X

will now sort as:

7
A
C
~