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.

5 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, 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
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base