Sort order with numbers as strings

All we need is an easy explanation of the problem, so here it is.

For demonstration purposes, I have a sample table with a varchar column called numberstring with the values:

-4
32
0
4
16
8
-8
1024

When selecting using ORDER BY numberstring, I find that different users get different results.

Some get:

-4, -8, 0, 1024, 16, 32, 4, 8

while some get:

0, 1024, 16, 32, -4, 4, -8, 8

Somehow, the second result appears to ignore the minus sign, even though the data is otherwise sorted as string data.

As far as I can tell, the underlying server is a standard install of Microsoft SQL Server express, without any special options. The version is within the last few years, but users with the same version still report different results.

Is there something in the setup which affects the results, and how can I view this from SSMS?

The database server and SSMS are set up in Australia with only the defaults, as far as I am aware. The data type is varchar(max).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

String sort order is determined by the collation. Users in different locations expect data to be sorted differently and the collation codifies those expectations.

When not explicitly specified, collation for a column is inherited from the database/instance level. Installing SQL Server sets a different instance-level collation, depending what the user chose or the default inferred from Windows language settings. From the documentation:

The server collation is specified during SQL Server installation. The default server-level collation is based upon the locale of the operating system.

For example, the default collation for systems using US English (en-US) is SQL_Latin1_General_CP1_CI_AS. For more information, including the list of OS locale to default collation mappings, see the "Server-level collations" section of Collation and Unicode Support.

Likely some of your users are using the backward-compatible SQL_Latin1_General_CP1_CI_AS collation by default, whereas others elsewhere in the world might be using something like Latin1_General_100_CI_AS:

DECLARE @T table 
(
    numberstring varchar(11) COLLATE Latin1_General_100_CI_AS NOT NULL
);

INSERT @T (numberstring) VALUES
('-4'),
('32'),
('0'),
('4'),
('16'),
('8'),
('-8'),
('1024');

SELECT * FROM @T AS T ORDER BY T.numberstring;
numberstring
0
1024
16
32
4
-4
8
-8
DECLARE @T table
(
    numberstring varchar(11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
);

INSERT @T (numberstring) VALUES
('-4'),
('32'),
('0'),
('4'),
('16'),
('8'),
('-8'),
('1024');

SELECT * FROM @T AS T ORDER BY T.numberstring;
numberstring
-4
-8
0
1024
16
32
4
8

db<>fiddle

This may have come about because some of your users have their Windows locale set to English (United States) while others have English (Australia).

One way to address this issue in your scripts is to use the optional COLLATE clause on your CREATE DATABASE statement (documentation).


You can see the default collation for each database using:

SELECT D.[name], D.collation_name 
FROM sys.databases AS D
ORDER BY D.[name];

If that returns a NULL, chances are the database is not online or is closed due to AUTO_CLOSE (which defaults to ON for SQL Server Express). See the documentation for sys.databases.

The instance-level collation with:

SELECT SERVERPROPERTY('Collation');

The older SQL_* collations use different rules for Unicode and non-Unicode data:

SELECT FH.* 
FROM sys.fn_helpcollations() AS FH
WHERE FH.[name] IN (N'SQL_Latin1_General_CP1_CI_AS', N'Latin1_General_100_CI_AS');
name description
Latin1_General_100_CI_AS Latin1-General-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
SQL_Latin1_General_CP1_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply