CODEWITHSUNDEEP
sqlsql-servert-sql
Makla
Makla

Reputation: 10459

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

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

Upvotes: 0

Views: 188

Answers (3)

Wiimm
Wiimm

Reputation: 3572

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

Upvotes: 1

Peter B
Peter B

Reputation: 24280

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

Upvotes: 4

See You in Iran
See You in Iran

Reputation: 11

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

Upvotes: 1

Related Questions