title: Alphanumeric Sort
body: <p>I need quick help in sorting data at SQL side. I am using <code>Sqlserver 2012</code> (good if answer give with the new feature).</p>
  
  <p>I already search some links as  <a href="http://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/" rel="nofollow">Sorting in alphanumeric</a> , <a href="http://www.codeproject.com/Articles/51602/How-to-Sort-Alphanumeric-Data-in-SQL" rel="nofollow">Alphanumeric string Sorting in Sqlserver - Code project</a>. But does not give the desired result.</p>
  
  <p>Still What I have try :</p>
  
  <pre><code>CREATE TABLE dbo.Section
      (
             Section varchar(50) NULL
      )
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsit no.43')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsit no.41')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 11')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 1')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 12')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 2')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 3')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 4')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 40')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite No. 41')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite no.20')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Campsite no.41')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Cabin')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Group Tent Campsite')
      INSERT INTO dbo.Section (Section.Section) VALUES ('Tent Campsite')
      INSERT INTO dbo.Section (Section.Section) VALUES ('test1')
      INSERT INTO dbo.Section (Section.Section) VALUES ('test2')
      INSERT INTO dbo.Section (Section.Section) VALUES ('test11')
      SELECT Section
      FROM dbo.Section
      --Show normal Sort
      SELECT Section
      FROM dbo.Section
      ORDER BY Section
      --Show AlphaNumberic Sort
      SELECT Section
      FROM dbo.Section
      ORDER BY LEFT(Section,PATINDEX('%[0-9]%',Section)), -- alphabetical sort
               CONVERT(varchar(50),SUBSTRING(Section,PATINDEX('%[0-9]%',Section),LEN(Section))) -- numerical sort
      --cleanup our work
      --DROP Table dbo.Section
  </code></pre>
  
  <p>Now what I want is : <strong>if same string find in alphabet part sort on that first and then numeric (consider space also if possible or you may give the result without space like Campsite no.41 and Campsite No. 41 will give in same order)</strong></p>
  
  <pre><code>Actual Result          Expected Result
  Campsit no.41          Campsit no.41
  Campsit no.43          Campsit no.43
  Campsite No. 1         Campsite No. 1
  Campsite No. 11        Campsite No. 2
  Campsite No. 12        Campsite No. 3
  Campsite No. 2         Campsite No. 4
  Campsite No. 21        Campsite No. 11
  Campsite No. 3         Campsite No. 12
  Campsite No. 4         Campsite No. 21
  Campsite No. 40        Campsite No. 40
  Campsite No. 41        Campsite No. 41
  Campsite no.20         Campsite no.20 --this will good to come here, if possible or if not, then remove space and set approriate
  Campsite no.41         Campsite no.41 --this will good to come here, if possible or if not, then remove space and set approriate
  Group Tent Campsite    Group Tent Campsite
  Tent Campsite          Tent Campsite
  test1                  test1
  test11                 test2
  test2                  test11
  </code></pre>