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?

Store relations instead, instead of . separated items as column values? — jarlh, Apr 17 '15 at 08:13
I can only perform a SELECT and have no possibility to store relations — h0mebrewer, Apr 17 '15 at 08:20
http://stackoverflow.com/questions/6446000/properly-sorting-dotted-numbers-stored-as-character-in-sql-server — niyou, Apr 17 '15 at 08:27

gotqn · Accepted Answer · 2015-04-17T11:19:34.120

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);

Works only with SQL Server 2012 and above. IIF isn't available at SQL Server 2008 — h0mebrewer, Apr 17 '15 at 11:14
@h0mebrewer You can easily change the `IIF` with `CASE WHEN` - check the edit. — gotqn, Apr 17 '15 at 11:20

score -1 · Answer 2 · answered Apr 17 '15 at 08:31

-1

remove dots and order:

select column from table order by replace(column,'.','')

And there is no need to convert varchar to number if you want 10 to come before 2.

answered Apr 17 '15 at 08:31

Ali Sheikhpour

10,475
5
41
82

I don't want 10 for 2. – h0mebrewer Apr 17 '15 at 08:37

Sorting hierarchical text in SQL

2 Answers2