Order by on Varchar field in sql server


Saturday, March 17, 2012

Hello Reader,

In this articles, i am going to explain you, how to use Order by on Varchar Fields in SQL Server,

Till now you might have implemented order by on Int Fields but you may find difficult to implement order by on Varchar fields.


In Image you can see, i have create one field with Name ONO as varchar(50)..

Now if you will do directly order by like shown below, then you will get wrong data.

This way is wrong for Varchar Field..
select * from db_product DP
order by ONO

You will get wrong data as Output..



The correct way is there..
select * from db_product DP
order by 
case 
    IsNumeric(DP.ONO) when 1 then 
        Replicate(Char(0), 100 - Len(DP.ONO)) + DP.ONO 
    else 
        DP.ONO 
end


Thanks Hope this post will help you, if yes please put comment below of this page,
Rajesh Singh,
Asp.Net Developer
Email: raj143svmit@gmail.com
Dobazaar (Dubai, UAE)
www.dobazaar.com