Wednesday, April 05, 2006

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.

2 comments:

Anonymous said...

But the code You have Posted doesn't works when the value of field contains alphabets

Regards
Anu

Anonymous said...

Another thing is, if the values are 1,1.1,1.2,2,2.1,2.2 and 2.1.1, then the 2.1.1 comes after 2.2. But it should appear after 2.1 not 2.2!