Sorting decimal values within a varchar field ...

Lets assume that you have data like '1.1.11', '1.1.3','4.1.2' etc within a column in a table. If you do
Select * from TableName
order by FieldName

it won't give you the data in the correctly sorted order.

Table Structure:

Create table tblSortIndexNumbers
(
sno int identity,
IndexNumber varchar(100)
)
Go

Insert scripts for generating sample data within our test table:

Insert into tblSortIndexNumbers values ('1.1.1')
Insert into tblSortIndexNumbers values ('2.1.1')
Insert into tblSortIndexNumbers values ('3.1.1')
Insert into tblSortIndexNumbers values ('1.1.3')
Insert into tblSortIndexNumbers values ('1.1.2')
Insert into tblSortIndexNumbers values ('1.2.1')
Insert into tblSortIndexNumbers values ('1.1.4')
Insert into tblSortIndexNumbers values ('1.2.2')
Insert into tblSortIndexNumbers values ('1.3.1')
Insert into tblSortIndexNumbers values ('2.2.1')
Go

Now, try running the below script and understand the problem I am trying to address:

Select sno, IndexNumber from tblSortIndexNumbers
Order by IndexNumber

Now that you have seen the actual problem .... let us see the correct way of sorting it also.

Code snippet to sort it properly

Select sno, IndexNumber from tblSortIndexNumbers
Order by
Convert (int, parseName(IndexNumber,3)),
Convert (int, parseName(IndexNumber,2)),
Convert (int, parseName(IndexNumber,1))

I got this idea from MS SQL Programming Newsgroup.