title: Natural (human alpha-numeric) sort in Microsoft SQL 2005
body: <p>We have a large database on which we have DB side pagination. This is quick, returning a page of 50 rows from millions of records in a small fraction of a second.</p>
  
  <p>Users can define their own sort, basically choosing what column to sort by.  Columns are dynamic - some have numeric values, some dates and some text.</p>
  
  <p>While most sort as expected text sorts in a dumb way. Well, I say dumb, it makes sense to computers, but frustrates users.</p>
  
  <p>For instance, sorting by a string record id gives something like:</p>
  
  <pre><code>rec1
  rec10
  rec14
  rec2
  rec20
  rec3
  rec4
  </code></pre>
  
  <p>...and so on.</p>
  
  <p>I want this to take account of the number, so:</p>
  
  <pre><code>rec1
  rec2
  rec3
  rec4
  rec10
  rec14
  rec20
  </code></pre>
  
  <p>I can't control the input (otherwise I'd just format in leading 000s) and I can't rely on a single format - some are things like "{alpha code}-{dept code}-{rec id}".</p>
  
  <p>I know a few ways to do this in C#, but can't pull down all the records to sort them, as that would be to slow.</p>
  
  <p>Does anyone know a way to quickly apply a natural sort in Sql server?</p>
  
  <hr>
  
  <p>We're using:</p>
  
  <pre><code>ROW_NUMBER() over (order by {field name} asc)
  </code></pre>
  
  <p>And then we're paging by that.</p>
  
  <p>We can add triggers, although we wouldn't. All their input is parametrised and the like, but I can't change the format - if they put in "rec2" and "rec10" they expect them to be returned just like that, and in natural order.</p>
  
  <hr>
  
  <p>We have valid user input that follows different formats for different clients.</p>
  
  <p>One might go rec1, rec2, rec3, ... rec100, rec101</p>
  
  <p>While another might go: grp1rec1, grp1rec2, ... grp20rec300, grp20rec301</p>
  
  <p>When I say we can't control the input I mean that we can't force users to change these standards - they have a value like grp1rec1 and I can't reformat it as grp01rec001, as that would be changing something used for lookups and linking to external systems.</p>
  
  <p>These formats vary a lot, but are often mixtures of letters and numbers.</p>
  
  <p>Sorting these in C# is easy - just break it up into <code>{ "grp", 20, "rec", 301 }</code> and then compare sequence values in turn.</p>
  
  <p>However there may be millions of records and the data is paged, I need the sort to be done on the SQL server.</p>
  
  <p>SQL server sorts by value, not comparison - in C# I can split the values out to compare, but in SQL I need some logic that (very quickly) gets a single value that consistently sorts.</p>
  
  <p>@moebius - your answer might work, but it does feel like an ugly compromise to add a sort-key for all these text values.</p>