Over at Experts-Exchange, I saw a question I get every once in a while.  It all has to do with how SQL Server orders data in a VARCHAR column.  SQL Server tries to sort a VARCHAR column in a dictionary order, 0 comes before 1.  The problem is you may have mixed numbers in your column (01 and 1), these should be sorted in the same order.  01 and 1 should both come before 2.

This leads the user to ask, how do I sort this in numeric order?

Let’s look at an example as we solve this problem.

CREATE TABLE tableName (
 columnName VARCHAR(20)
)
INSERT INTO tablename
SELECT 1 UNION
SELECT '01' UNION
SELECT '02' UNION
SELECT '10' UNION
SELECT '11'

With this table and data, if we run the following query, we see the data is sorted in alphabetical (dictionary) order.

SELECT columnName
FROM tableName
ORDER BY columnName
columnName
----------
01
02
1
10
11

But, if we alter the ORDER BY clause, casting the column as an integer, then we get the results we would expect.

SELECT columnName
FROM tableName
ORDER BY CONVERT(INT, columnName)
columnName
----------
01
1
02
10
11

This does bring up an important warning.  If your column contains any alpha characters (not 0-9), then this statement will fail.  You cannot cast “a” to an integer.  But there is a solution.  If you are willing to completely ignore non integer data, you can use the following function and query to order your results in the order you wish.  First create the following function in your database.

IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'getIntPortion')
 DROP FUNCTION getIntPortion
GO    
CREATE FUNCTION dbo.getIntPortion ( @inputVarchar varchar(255))
RETURNS VARCHAR(255)
AS
BEGIN
 WHILE(PATINDEX('%[^0-9]%', @inputVarchar) ) > 0
 BEGIN
 --then remove that one character, then continue
 SET @inputVarchar = REPLACE(@inputVarchar
 , SUBSTRING(@inputVarchar, PATINDEX('%[^0-9]%', @inputVarchar), 1)
 , '')
 END
 RETURN @inputVarchar
END

Then, you can use the following query to get the results in numeric order.

SELECT columnName
FROM tableName
ORDER BY CONVERT(INT, dbo.getIntPortion ( columnName))
columnName
----------
01
1
1a
02
10
11

If you have any questions about this solution, please let me know.  I’m here to help you learn SQL!

Reference

http://www.experts-exchange.com/Software/Server_Software/Email_Servers/Exchange/Q_25770999.html