Sorting a VarChar Attribute as a Number in a SQL Statement

This post is not particularly .NET, but I am posting it anyway.

I was needing to sort the results of a SQL statements by a Priority attribute (which has the values 1 to 10). Unfortunately, the Priority attribute is defined as VarChar, so 10 always follows 1 when you do an ORDER BY Priority.

There are many solutions to this problem, but this simplest (although not the more readily obvious, and maybe not the most efficient) is this:

SELECT * FROM Data_Table ORDER BY Len(Priority), Priority ASC

Another approach is:

SELECT * FROM Data_Table ORDER BY RIGHT('0000' + RTRIM(LTRIM(Priority)), 4)


There are other approaches in the posts where I found this solution:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81901

http://www.eggheadcafe.com/conversation.aspx?messageid=29907381&threadid=29907381