tsql - Properly sorting dotted numbers stored as character in SQL Server

Question

I have a SQL table that stores a custom item number. Each of these can have a child broken off from it with a separator of .. Each of those can have a child too.

An example of what it could be (again, dynamic, don't know what it will be):

Item Number
1
1.1
1.1.1
1.1.1.1
1.1.1.1.a
1.1.1.1.b
10
11
2.1
2.10
2.2
2.20
20
3
30

The thing that makes this tough is those numbers are created on the fly and not necessarily in order. You may create 5 numbers (1, 2, 3, 4, 5) and then create a child of 1 so it will not be stored in order in the db.

How do I select from the table and order by the Item Number so that it shows properly, as above, when the data isn't stored in that order?

Most solutions I've tried either gives me 1, 2, 3, 4, 5...1.1, 1.2 OR 1, 1.1, 1.1.1, 10, 11...2, 2.1, 20....3, 30, etc.

Answer 1

If you have SQL 2008 you can use the new hierarchyid data type:

WITH Items (ItemNumber) AS (
    SELECT '1' UNION ALL SELECT '1.1' UNION ALL SELECT '1.1.1'
    UNION ALL SELECT '10' UNION ALL SELECT '11' UNION ALL SELECT '2'
    UNION ALL SELECT '2.1' UNION ALL SELECT '20' UNION ALL SELECT '3'
    UNION ALL SELECT '30'
)
SELECT *
FROM Items 
ORDER BY Convert(hierarchyid, '/' + ItemNumber + '/');