Differences between Excel and SQL sorting

Question

Used programs:

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 a user has the same data in an Excel spreadsheet and is sorted 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 there is a discrepancy between sorting in SQL and Excel (except, "because it's Microsoft").

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

Answer 1

SQL- , . ( 1). , Microsoft, Excel , -. , ( ), .

SQL Server, :

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

, , , 1 , , 2 , ( 0 , ). , / , , Excel.

Answer 2

SQL Server, Excel, "" ( , ).

, Excel - : - Excel.