Sorting Numbers in VarChar field

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

Related posts:

1. Converting VARCHAR to DATETIME
2. SQL 201 – Primary Keys
3. SQL 201 – Locking Hints
4. SQL 201 – CLUSTERED vs. NONCLUSTERED Indexes
5. SQL 101 – CREATE TABLE

About Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.