Sort nvarchar column in t-Sql

Question

I have an nvarchar column with the following data:

1.0  
10.0      
10.1      
5.1       
6.4       
5.3       
90.5      
39.23     
23.2.2    
21.2.1    
4.3.1 

When I order it as a string, I get the following result:

1.0       
10.0      
10.1      
21.2.1    
23.2.2    
39.23     
4.3.1     
5.1       
5.3       
6.4       
90.5  

select * from mytable order by mycolumn

which is wrong, it should sort the set as numeric, for example:

1.0
1.1
1.2
2.3
2.3.1

etc..

How can I sort it correctly? I would be grateful for any help!

Answer 1

If you need to sort by numbers, it is better to store data in the form of numbers. Save the details in three columns, then you can query ORDER BY n1, n2, n3.
You can add computed columnto return a full string, for example. with formula

=convert(nvarchar(10), n1) + '.' + convert(nvarchar(10), n2) + 
CASE WHEN n3 IS NOT NULL THEN '.' + 
convert(nvarchar(10), n3) ELSE '' END

, dbms , "" .

EDIT

, ,

ORDER BY CONVERT(HIERARCHYID, '/' + myColumn + '/')