title: How to properly sort alphanumeric in SQL Server 2000
body: <p>MS SQL Server 2000</p>
  
  <p>I have a column in Table A called Name. I wish to sort the Name field. Many but not all of the records for Name start will KL and are followed by a number (KL 1234, KL 2, KL 323, etc).</p>
  
  <h2>Table A</h2>
  
  <h2>Name</h2>
  
  <p>Able<br>
  Bravo<br>
  KL 2<br>
  KL 323<br>
  KL 1234<br>
  Zebra</p>
  
  <p>If I use</p>
  
  <pre><code>Select Name from A 
  Order by Name
  </code></pre>
  
  <p>I get</p>
  
  <p>Able<br>
  Bravo<br>
  KL 1234<br>
  KL 2<br>
  KL 323<br>
  Zebra  </p>
  
  <p>I want</p>
  
  <p>Able<br>
  Bravo<br>
  KL 2<br>
  KL 323<br>
  KL 1234<br>
  Zebra  </p>
  
  <p>If they all started with KL I could use</p>
  
  <pre><code>Select Name from A
  Order by cast(replace(name, 'KL', '') as big int)
  </code></pre>
  
  <p>but this generates an "unble to cast name as big int" error for values that do not start with KL</p>
  
  <p>Thanks for any help.</p>