Want a sensible, efficient means of sorting numbers in strings as actual numbers? Consider voting for my Microsoft Connect suggestion: Support "natural sorting" / DIGITSASNUMBERS as a Collation option
While this Question is specific to SQL Server, and this Answer is not, I felt that I should still post this information simply to raise awareness of it and not to be in opposition of any of the other answers.
That being said, outside of SQL Server, in certain environments it is possible to do this type of sorting. It is something that is at least specified in the Unicode documentation. In the UNICODE LOCALE DATA MARKUP LANGUAGE (LDML)
PART 5: COLLATION standard / report, there is a chart for Collation Settings that describes various options for tailoring the sorting behavior. One of the options is
If set to on, any sequence of Decimal Digits (General_Category = Nd in the [UAX44]) is sorted at a primary level with its numeric value. For example, "A-21" < "A-123". The computed primary weights are all at the start of the digit reordering group. Thus with an untailored UCA table, "a$" < "a0" < "a2" < "a12" < "a⓪" < "aa".
However, this document is a "technical standard" and not a part of the core Unicode specification. A note at the top of the document states:
A Unicode Technical Standard (UTS) is an independent specification. Conformance to the Unicode Standard does not imply conformance to any UTS.
Hence, this particular behavior is not available in SQL Server or even in .NET (at least not natively), even though both do conform to the core Unicode specification.
The ICU project (International Components for Unicode) is a set of C/C++ and Java libraries that implements this functionality, and there is even an online demo of it. And under "related projects" there is a link to a .NET project that seems to be a COM object wrapper for the ICU library that would allow this functionality to be exposed to managed code. But it is not clear if that .NET project is still active.
But to see this behavior in action, go to the ICU Collation Demo.
Paste the following into the Input text area on the left side:
Set all options to "default". Check the "input line numbers" option to the right of the sort button, and make sure that the "diff strengths" option is un-checked.
Click the sort button and you should get back the following:
This is what should be expected when doing a typical string sort, and what you are seeing in SQL Server.
Now, in the series of radio buttons just above the sort button, the second row is labeled "numeric". Select the "on" radio button.
Click the sort button again and you should get back the following:
Questioning if this works when the numeric portion is in the middle of the string? Ok, paste the following into the Input text area on the left side (replacing the previous list):
Script - 1.sql
Script - 2.sql
Script - 10B.sql
Script - 6.sql
Script - 11.sql
Script - 10A.sql
Script - 3.sql
Script - 10.sql
Make sure that the numeric setting is still set to "on". Click the sort button again and you should get back the following:
 Script - 1.sql
 Script - 2.sql
 Script - 3.sql
 Script - 6.sql
 Script - 10.sql
 Script - 10A.sql
 Script - 10B.sql
 Script - 11.sql
Want to see this in another place? Create a folder on your harddrive, something like C:\temp\sorting\, and create empty files of those same "Script -..." names. Do a
DIR in a command window and you will see the standard sorting. But when looking at the list of files in Windows Explorer you will see the list sorted using the "numeric" option :-).