Differences between Excel and SQL sorting

Question

Programs used:

SQL Server 2000, Excel 2003

We have a table in our database called Samples. Using the following query...

SELECT [Sample], [Flag] FROM Samples
ORDER BY [Sample]

... we get the following results:

 Sample    Flag
----------   ----
12-ABC-345      1
123-45-AB       0
679-ADC-12      1

When the user has the same data in an Excel spreadsheet, and sorts by the Sample column, they get the following sort order:

 Sample    Flag
----------   ----
123-45-AB       0
12-ABC-345      1
679-ADC-12      1

Out of curiosity, why is there a discrepancy between the sort in SQL and Excel (other than, "because it's Microsoft").

Is there a way in SQL to sort on the Sample column in the same method as the Excel method, or vice versa?

Answer 1

The SQL server sorting is determined by the database, table, or field collation. By default, this is a standard lexicographical string sort (the character code for the hyphen is numerically lower than the character code for 1). Unfortunately, according to this Microsoft link, Excel ignores hyphens and apostrophes when sorting, except for tie-breaking. There's no collation that does this specifically (that I'm aware of), so you'll have to fake it.

To achieve the same result in SQL Server, you'd need to do:

SELECT [Sample], [Flag] FROM Samples
ORDER BY REPLACE(REPLACE([Sample], '-', ''), '''', ''), 
    (CASE WHEN CHARINDEX([Sample], '-') > 0 THEN 1 ELSE 0 END) + 
    (CASE WHEN CHARINDEX([Sample], '''') > 0 THEN 1 ELSE 0 END) ASC

This orders the results by the string as if it had all hyphens and apostrophe's removed, then orders by a computed value that will yield 1 for any value that contains a hyphen or an apostrophe, or 2 for any value that contains both (and 0 for a value that contains neither). This expression will cause any value that contains a hyphen and/or apostrophe to sort after an expression that is otherwise equivalent, just like Excel.

Answer 2

I personally consider SQL Server sorting order correct and I'd intervene on Excel, as it's the one following an "unusual" method (at least, from my experience).

Here's an explanation of how Excel sorts alphanumeric data, and how to fix it: How to correctly sort alphanumeric data in Excel.