Strange sort behaviour for numeric values in Varchar column in SQL Server

Question

Can someone please explain this strange behaviour:

select a from (
    select '1' as a
    union all
    select '2' as a
    union all
    select '-3' as a
) as b
order by a desc

select a from (
    select '4' as a
    union all
    select '5' as a
    union all
    select '-3' as a
) as b
order by a desc

Result for query 1:

-3
2
1

Result for query 2:

5
4
-3

It looks like - character is ignored. I though, that SQL Server orders varchars based on ASCII code.

So expected result would be:

2
1
-3   //ascii - is before 0-9

and:

 5
 4
-3  //ascii - is before 0-9

I get same result if I add a letter before number:

select a from (
    select 'a1' as a
    union all
    select 'a2' as a
    union all
    select '-a3' as a
) as b
order by a desc

select a from (
    select 'a4' as a
    union all
    select 'a5' as a
    union all
    select '-a3' as a
) as b
order by a desc

I suspect the collation setting will be important. It's almost always better to control Sort Order with an explicit numeric SortOrder column that you define. — Mitch Wheat, Feb 05 '19 at 09:20
Possible duplicate of [SQL Sorting and hyphens](https://stackoverflow.com/questions/360950/sql-sorting-and-hyphens)? — Thom A, Feb 05 '19 at 09:22
String value '1000' will be sorted before '9', while numerical value 9 will be sorted before 1000. — jarlh, Feb 05 '19 at 09:32
@Makla: I have checked your query and it gives me perfect result — Chetan Sanghani, Feb 05 '19 at 09:38

Peter B · Accepted Answer · 2019-02-05T09:37:37.017

4

Actual sort order in SQL Server depends totally on the active collation (either the default one, or a collation that is specified explicitly).

If e.g. you use a binary collation, you'll get what you were expecting for this case:

select a from (
    select '1' as a
    union all
    select '2' as a
    union all
    select '-3' as a
) as b
order by a COLLATE Latin1_General_BIN desc
/* Result: 2, 1, -3  */

select a from (
    select '4' as a
    union all
    select '5' as a
    union all
    select '-3' as a
) as b
order by a COLLATE Latin1_General_BIN desc
/* Result: 5, 4, -3  */

To see all collations, run this:

select * from sys.fn_helpcollations()

edited Feb 05 '19 at 09:37

answered Feb 05 '19 at 09:27

Peter B

22,460
5
32
69

Thanks @PeterB, it would be awesome to explain (share a link) why binary collation acts like it does. (I will try to add this information myself if I found it.) – Makla Feb 05 '19 at 09:48
1

Here is an article with some more info on binary collations: https://mikerodionov.com/2018/06/sql-servers-binary2-collations/ – Peter B Feb 05 '19 at 10:14

score 1 · Answer 2 · answered Feb 05 '19 at 09:35

1

You should set the collation to Latin1_General_BIN like this :

select a from (
select '1' as a
union all
select '0' as a
union all
select '-1' as a
) as b
order by a COLLATE Latin1_General_BIN desc

answered Feb 05 '19 at 09:35

See You in Iran

11
2

1

How is this different from Peter B answer? – Makla Feb 05 '19 at 09:37

score 1 · Answer 3 · answered Feb 05 '19 at 09:38

1

If you use numbers instead of strings ...

select a from (
    select 1 as a
    union all
    select 2 as a
    union all
    select -3 as a
) as b
order by a desc

... then the numbers are sorted as expected:

2
1
-3

answered Feb 05 '19 at 09:38

Wiimm

2,971
1
15
25

Of course but I don't have number. I also don't have values 2, 3 and -1. This was simplified example. – Makla Feb 05 '19 at 09:46

Strange sort behaviour for numeric values in Varchar column in SQL Server

3 Answers3