Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Support "natural sorting" / DIGITSASNUMBERS as a Collation option

Quite often** people want to sort a string containing either only numbers, or a mix of letters and numbers in a pattern, such that the numeric portion of that string is sorted as an actual number. For example, "Class40g" would come before "Class300c", not after. This behavior has already been integrated into File Explorer, starting in Windows 7.

Currently when people need to do this type of sorting, they need to create silly and inefficient methods of parsing the string into separate alpha and numeric chunks.

** often = numerous questions regarding how to accomplish this type of sorting on MSDN Forums, StackOverflow, DBA.StackExchange, etc.

13 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 11

<=-=Jul 15 2016 6:51AM=-=>

I offer as evidence for the desire for this to happen, the 1k+ views of my stackoverflow question from 2011. A little under a person a day.

http://dba.stackexchange.com/questions/3828/is-there-a-collation-to-sort-the-following-strings-in-the-following-order-1-2-3?noredirect=1#comment273118_3828

<=-=Jul 15 2016 8:40AM=-=>

Adding to the link that Justin posted to his Stack Overflow question:

http://dba.stackexchange.com/questions/117379/sort-a-varchar-type-column
http://dba.stackexchange.com/questions/126744/order-by-and-comparison-of-mixed-strings-of-letters-and-numbers

http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005
http://stackoverflow.com/questions/3158917/natural-sort-for-sql-server
http://stackoverflow.com/questions/22461619/complex-sort-of-field-string-number-string
http://stackoverflow.com/questions/24989365/sorting-a-string-numerically-in-sql-server
http://stackoverflow.com/questions/26552435/sorting-mixed-numbers-and-letters
http://stackoverflow.com/questions/26932310/sql-server-string-convert-numeric-and-sorting-issue
http://stackoverflow.com/questions/28278467/sort-dropdown-list-in-alpha-numeric-order
http://stackoverflow.com/questions/28372359/natural-sort-for-sql-server
http://stackoverflow.com/questions/29676432/alphanumeric-sort
http://stackoverflow.com/questions/30502118/sort-float-numbers-as-a-natural-numbers-in-sql-server
http://stackoverflow.com/questions/35001818/alphanumeric-and-numeric-sorting-in-sql-server
http://stackoverflow.com/questions/37728476/sql-sorting-numeric-and-string

http://www.codeproject.com/Articles/51602/How-to-Sort-Alphanumeric-Data-in-SQL

<=-=Mar 3 2017 1:02PM=-=>

We understand the scenario and if it gets more votes we will consider it for the next releases. It is assigned to our collation & string processing team.

1 comment

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base