SQL Server ordering SQL Server does not match ASCII code
I am using SQL Server 2012 and I have a sorted database SQL_Latin1_General_CP1_CI_AS
:
create table testtable (c nvarchar(1) null)
insert into testtable (c) values('8')
insert into testtable (c) values('9')
insert into testtable (c) values(':')
insert into testtable (c) values(';')
select c, ASCII(c) ascvalue
from testtable
order by c
I am getting this output:
c | ascvalue
------------
: | 58
; | 59
8 | 56
9 | 57
Why is SQL Server sorting in the opposite order of ascii code?
source to share
Thanks Ross et al! Found the following documentation which has a great explanation regarding MS SQL Server collation, thought I would post it here to help anyone who comes across this question or related questions.
Ordering Sorting specifies the bit patterns that represent each character in the dataset. Collisions also define the rules for sorting and comparing data. SQL Server supports storing objects that have different sorts in the same database. For non-Unicode columns, the collation parameter specifies the code page for the data and what characters can be represented. Data that moves between non-Unicode columns must be converted from the source code page to the destination code page.
Read more here http://goo.gl/RpBGWN
From my code snippet, if I wanted to sort the value in binary, the query could be changed to the following:
select c, ASCII(c) ascvalue from testtable order by c collate Latin1_General_BIN
Or change the collation definition when creating the table
create table testtable (c nvarchar(1) collate Latin1_General_BIN null)
This gives the following output:
c | ascvalue ------------ 8 | 56 9 | 57 : | 58 ; | 59
source to share
The comparison is not binary. It is CI case insensitive, so it will add upper and lower case characters to sort them. It defines the sort order of punctuation and non-ASCII digits in the way you find it. It is a SQL collation that does what SQL mappings do: determine the sort order according to certain rules.
source to share
If you want the sorting to be in ASCII value
each character, you must specify that explicitly in Order by
.
select c, ASCII(c) ascvalue from #testtable order by ASCII(c)
else SQL_Latin1_General_CP1_CI_AS
reports that the supported language is English.
There is no BIN in the collation name, which means it supports dictionary collation
sorting dictionaries; comparison of character data is based on dictionary order ("A" and "a" <B and b).
The default word order is when no other ordering is explicitly defined
CI
means that character data is case insensitive (it means "ABC" = "abc").
AS
means character data is accent sensitive ('à' <> 'ä').
source to share