title: How to sort and display mixed lists of alphas and numbers as the users expect?
body: <p>Our application has a <code>CustomerNumber</code> field.  We have hundreds of different people using the system (each has their own login and their own list of <code>CustomerNumber</code>s).  An individual user might have at most 100,000 customers.   Many have less than 100.</p>
  
  <p>Some people only put actual numbers into their customer number fields, while others use a mixture of things.  The system allows 20 characters which can be A-Z, 0-9 or a dash, and stores these in a VARCHAR2(20).  Anything lowercase is made uppercase before being stored.</p>
  
  <p>Now, let's say we have a simple report that lists all the customers for a particular user, sorted by Customer Number. e.g.</p>
  
  <pre><code>SELECT CustomerNumber,CustomerName
  FROM Customer
  WHERE User = ?
  ORDER BY CustomerNumber;
  </code></pre>
  
  <p>This is a naive solution as the people that only ever use numbers do not want to see a plain alphabetic sort (where "10" comes before "9").</p>
  
  <p>I do not wish to ask the user any unnecessary questions about their data.</p>
  
  <p>I'm using Oracle, but I think it would be interesting to see some solutions for other databases.  Please include which database your answer works on.</p>
  
  <p>What do you think the best way to implement this is?</p>