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!