Sorting hierarchical text in SQL

Question

I've a table with a varchar field. This field stores hierarchical numbers which I want to order.

Here is an example of my data:

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

How can I achieve the following result with T-SQL:

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

I tried to split up the parts with the following SQL, but that's not elegant.

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),'.',''))  > 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

Is theree a better way to do this?

Answer 1

Try this:

DECLARE @DataSource TABLE
(
    [Value] VARCHAR(12)
);

INSERT INTO @DataSource ([Value])
VALUES ('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');

SELECT *
FROM @DataSource
ORDER BY CAST('/' + IIF(RIGHT([Value],1) = '.', LEFT([Value], LEN([Value]) - 1), [Value]) + '/' AS HIERARCHYID);

You can check the hierarchyid for more details. The check in the ORDER BY clause is just removing the . at the end of the value (if such exist).

---

You can change the IIF function with simple CASE WHEN like this:

SELECT *
FROM @DataSource
ORDER BY CAST('/' + CASE WHEN  RIGHT([Value],1) = '.' THEN LEFT([Value], LEN([Value]) - 1) ELSE [Value] END + '/' AS HIERARCHYID);