title: Natural (human alpha-numeric) sort in Microsoft SQL Server
body: <p>Thank you for taking time to read all this, its a lot!  Appreciate all you fellow enthusiasts!</p>
  
  <p>How to natural sort?</p>
  
  <p>ie. order a set of alpha numeric data to appear as: </p>
  
  <pre><code>Season 1, Season 2, Season 10, Season 20
  </code></pre>
  
  <p>instead of </p>
  
  <pre><code>Season 1, Season 10, Season 2, Season 20
  </code></pre>
  
  <p>I use a very practical example of tv seasons in a very practical format as case.</p>
  
  <p>I am looking to accomplish the following:</p>
  
  <ol>
  <li>Share my working solution for others</li>
  <li>Ask your help in figuring how to shorten it (or find better solution) to my solution</li>
  <li>Can you solve criteria 7 below? </li>
  </ol>
  
  <p>I spent about 2 hours researching online and another 3 hours building this solution. Some of the reference material came from:</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005">SO Post</a></li>
  <li><a href="https://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/" rel="noreferrer">MSDN</a></li>
  <li><a href="https://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/" rel="noreferrer">Essential SQL</a></li>
  <li><a href="https://www.codeproject.com/Articles/842541/How-Do-I-Use-SQL-Server-to-Sort-Alphanumeric-Value" rel="noreferrer">Code Project</a></li>
  <li><a href="https://dba.stackexchange.com/questions/14831/natural-numeric-sort-on-string">DBA Stack Exchange</a></li>
  </ul>
  
  <p>Some of the solutions found on SO and other sites only work for 90% of cases. However, most/all do NOT work if you have multiple numeric values in your text, or will cause SQL error if there isn't a number found in the text at all.</p>
  
  <p>I have created this <a href="http://sqlfiddle.com/#!18/70b23/1/0" rel="noreferrer">SQLFiddle</a> link to play around with (includes all below code).</p>
  
  <p>Here is the create statement:</p>
  
  <pre><code>create table tvseason
  (
      title varchar(100)
  );
  
  insert into tvseason (title)
  values ('100 Season 03'), ('100 Season 1'),
         ('100 Season 10'), ('100 Season 2'),
         ('100 Season 4'), ('Show Season 1 (2008)'),
         ('Show Season 2 (2008)'), ('Show Season 10 (2008)'),
         ('Another Season 01'), ('Another Season 02'),
         ('Another 1st Anniversary Season 01'),
         ('Another 2nd Anniversary Season 01'),
         ('Another 10th Anniversary Season 01'),
         ('Some Show Another No Season Number'),
         ('Some Show No Season Number'),
         ('Show 2 Season 1'),
         ('Some Show With Season Number 1'),
         ('Some Show With Season Number 2'),
         ('Some Show With Season Number 10');
  </code></pre>
  
  <p>Here is my working solution (only unable to solve criteria #7 below):</p>
  
  <pre><code>select 
      title, "index", titleLeft,
      convert(int, coalesce(nullif(titleRightTrim2, ''), titleRight)) titleRight
  from
      (select 
           title, "index", titleLeft, titleRight, titleRightTrim1,
           case 
              when PATINDEX('%[^0-9]%', titleRightTrim2) = 0 
                 then titleRightTrim2
                 else left(titleRightTrim2, PATINDEX('%[^0-9]%', titleRightTrim2) - 1)
           end as titleRightTrim2
       from
           (select
                title, 
                len(title) - PATINDEX('%[0-9] %', reverse(title)) 'index',
                left(title, len(title) - PATINDEX('%[0-9] %', reverse(title))) titleLeft,
                ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))) titleRight,
                ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))) titleRightTrim1,
                left(ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))), PATINDEX('% %', ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))))) titleRightTrim2
            from
                tvseason) x) y
  order by 
      titleLeft, titleRight
  </code></pre>
  
  <p>Criteria to consider:</p>
  
  <ol>
  <li>Text contains no numbers</li>
  <li>Text contains numbers at beginning and end</li>
  <li>Text contains numbers at beginning only</li>
  <li>Text contains numbers at end only</li>
  <li>Text may contain (YYYY) at end</li>
  <li>Text may end with single digit OR double digit (ex. 1 or 01)</li>
  <li>Optional: Any combination of above, plus numbers in middle of text</li>
  </ol>
  
  <p>Here is the output:</p>
  
  <pre><code>title
  100 Season 1
  100 Season 2
  100 Season 03
  100 Season 4
  100 Season 10
  **Case 7 here**
  Another 10th Anniversary Season 01
  Another 1st Anniversary Season 01
  Another 2nd Anniversary Season 01
  Another Season 01
  Another Season 02
  Show (2008) Season 1
  Show (2008) Season 2
  Show 2 The 75th Anniversary Season 1
  Show Season 1 (2008)
  Show Season 2 (2008)
  Show Season 10 (2008)
  Some Show Another No Season Number
  Some Show No Season Number
  Some Show With Season Number 1
  Some Show With Season Number 2
  Some Show With Season Number 10
  </code></pre>