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
– Mitch Wheat, Commented Feb 5, 2019 at 9:20
String value '1000' will be sorted before '9', while numerical value 9 will be sorted before 1000. — jarlh
– jarlh, Commented Feb 5, 2019 at 9:32
@Makla: I have checked your query and it gives me perfect result — Chetan Sanghani
– Chetan Sanghani, Commented Feb 5, 2019 at 9:38

Peter B · Accepted Answer · 2019-02-05 09:37:37Z

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 5, 2019 at 9:37

answered Feb 5, 2019 at 9:27

Peter B

24.7k5 gold badges42 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Makla Over a year ago

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.)

Peter B Over a year ago

Here is an article with some more info on binary collations: mikerodionov.com/2018/06/sql-servers-binary2-collations

See You in Iran · Accepted Answer · 2019-02-05 09:35:30Z

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 5, 2019 at 9:35

See You in Iran

112 bronze badges

1 Comment

Makla Over a year ago

How is this different from Peter B answer?

Wiimm · Accepted Answer · 2019-02-05 09:38:05Z

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 5, 2019 at 9:38

Wiimm

3,7531 gold badge20 silver badges28 bronze badges

1 Comment

Makla Over a year ago

Of course but I don't have number. I also don't have values 2, 3 and -1. This was simplified example.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related