Sql query to sort numbers & to get output as 1.1.1,1.1.2,1.1.3

Question

I want sql query to get output as

1.1.1,1.1.2,1.1.3...

I tried order by clause but I am getting result as 1.1.1,1.1.10,1.1.11,1.1.2,1.1.3...

My procedure is:

select * 
  from Projects,
 where iId in (select value 
                 from ParmsToList(@projectid,',')
              )
   And Projects.categoryid  > 0
 order by Projects.vProjectName

Answer 1

One way is to make them look like hierarchy nodes

;with t(f) as (
    select '1.1.1'  union
    select '1.1.10' union
    select '1.1.11' union
    select '1.1.2'  union
    select '1.1.3'
)
select
    *
from 
    t
order by
    cast('/' + replace(f, '.', '/') + '/' as hierarchyid)

for

f
1.1.1
1.1.2
1.1.3
1.1.10
1.1.11

Answer 2

SQL Server 2008+:

select t.v
from (values ('1.1.1'), ('1.1.10'), ('1.1.11'), ('1.1.2'), ('1.1.3'), ('1.2.1'), ('10.1.1')) as t(v)
cross apply (
    select x = cast('<i>' + replace(v, '.', '</i><i>') + '</i>' as xml)
) x
order by x.value('i[1]','int'),x.value('i[2]','int'), x.value('i[3]','int')

SQL Server 2005+:

;with t(v) as (
    select '1.1.1' union all 
    select '1.1.10' union all 
    select '1.1.11' union all 
    select '1.1.2' union all 
    select '1.1.3' union all 
    select '1.2.1' union all 
    select '10.1.1'
)
select t.v
from t
cross apply (
    select x = cast('<i>' + replace(v, '.', '</i><i>') + '</i>' as xml)
) x
order by x.value('i[1]','int'),x.value('i[2]','int'), x.value('i[3]','int')

Output:

v
------
1.1.1
1.1.2
1.1.3
1.1.10
1.1.11
1.2.1
10.1.1