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

Tags: , ,

2 Responses to “Sorting Numbers in VarChar field”

  1. Karthik 20120813 at 08:18 #

    “SELECT columnName
    FROM tableName
    ORDER BY CONVERT(INT, dbo.getIntPortion ( columnName))”

    The above query is not working.. Shows the error message as

    “Msg 4121, Level 16, State 1, Line 1
    Cannot find either column “dbo” or the user-defined function or aggregate “dbo.GetIntPart”, or the name is ambiguous.”

    Please do help for me. Hopefully waiting for your reply.

    Thanks and Regards,

    Karthik.R.S

    • Shannon Lowder 20120820 at 05:28 #

      Did you run the create function call before running the SELECT statement? If not, then the function GetIntPart won’t be available. If you did run it, did you run in all in one GO statement? If so, you need to “fully” create the function before calling it.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.