title: Sorting Alphanumeric field in SQL CE (Compact Edition) version 3.5
body: <p>Sorting Alphanumeric field in SQL CE (Compact Edition) version 3.5</p>
  
  <p>TreeNumber is a nvarchar field with a mix of numbers and strings for the values. I want to sort these records so that the records that contain alpha characters are at the top and the rest are sorted in numeric order.</p>
  
  <p>I want something similar to the following query which works in SQL Server:</p>
  
  <pre><code>SELECT * FROM Tree
  ORDER BY 
      (CASE WHEN TreeNumber LIKE '%[a-z]%' THEN 0 ELSE TreeNumber END), TreeNumber
  </code></pre>
  
  <p>The above query doesn't seem to work because the [] range is not supported in CE. Another solution which works with SQL Server but doesn’t work in CE because "IsNumber()" is not supported is below:</p>
  
  <pre><code>SELECT * FROM Tree 
  ORDER BY 
      (CASE IsNumeric(TreeNumber) WHEN 0 THEN 0 ELSE TreeNumber END), TreeNumber
  </code></pre>