Sorting hierarchical text in SQL

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 @DataSourceORDER 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 @DataSourceORDER BY CAST('/' + CASE WHEN  RIGHT([Value],1) = '.' THEN LEFT([Value], LEN([Value]) - 1) ELSE [Value] END + '/' AS HIERARCHYID);

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.