title: Sorting hierarchical text in SQL
body: <p>I've a table with a <code>varchar</code> field. This field stores hierarchical numbers which I want to order.</p>
  
  <p>Here is an example of my data:</p>
  
  <pre><code>1
  1.1
  1.1.1
  1.1.2
  1.1.3.
  1.1.4
  1.1.5
  1.1.6.
  1.1.7
  10.
  10.1
  10.2
  10.3
  11.
  11.1
  11.2
  2.
  2.1
  1.2.2
  1.2.2.1
  1.2.2.2
  </code></pre>
  
  <p>How can I achieve the following result with <code>T-SQL</code>:</p>
  
  <pre><code>1
  1.1
  1.1.1
  1.1.2
  1.1.3
  1.1.4
  1.1.5
  1.1.6
  1.1.7
  1.2.2
  1.2.2.1
  1.2.2.2
  2.
  2.1
  10.
  10.1
  10.2
  10.3
  11.
  11.1
  11.2
  </code></pre>
  
  <p>I tried to split up the parts with the following SQL, but that's not elegant.</p>
  
  <pre><code>SELECT CASE WHEN CHARINDEX('.',SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.',''))  = 0
    THEN SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.','') 
    ELSE REPLACE(SUBSTRING(SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.',''),1, CHARINDEX('.',SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.',''))),'.','')
    END AS FIRST_PART
   ,CASE WHEN CHARINDEX('.',SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.',''))  &gt; 0
        THEN SUBSTRING( 
  SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.','') 
     ,CHARINDEX('.',SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.','')) +1
     ,LEN( SUBSTRING(LTRIM(RTRIM(NR)),1,LEN(NR)-1)+REPLACE(SUBSTRING(LTRIM(RTRIM(NR)),LEN(NR),1),'.','')  )
     )
    ELSE '0'
    END AS SECOND_PART
  FROM TEST_TABLE
  </code></pre>
  
  <p>Is theree a better way to do this?</p>