Question: How to Sort a Varchar Column Storing Integers with Order By?
Answer: I recently received a very interesting question during my Comprehensive Database Performance Health Check, one of the Senior Developer asked if I know how to Sort a Varchar Column Storing Integers with Order By?
The obvious answer would be to change the datatype but it is not always possible. Let us see two different solutions addressing this solution.
First thing first – let us create a sample dataset.
USE TEMPDB GO CREATE TABLE #NUMBERS(NUMBER VARCHAR(10)) INSERT INTO #NUMBERS(NUMBER) SELECT '8792' UNION ALL SELECT '199876' UNION ALL SELECT '9012' UNION ALL SELECT '876100' UNION ALL SELECT '2098763' SELECT NUMBER FROM #NUMBERS ORDER BY NUMBER
The result is
If you notice the result is not in the ascending order as long as the order is concerned. Since the user can’t change the datatype, I suggest the following methods
Method 1: USE CAST function in the ORDER BY Clause
SELECT NUMBER FROM #NUMBERS ORDER BY CAST(NUMBER AS INT)
Method 2: Multiply the column by 1 to implicitly convert them into numbers
SELECT NUMBER FROM #NUMBERS ORDER BY NUMBER*1
Both the above two queries result to the following
If you have any other methods, post them in the comments.
On a separate note, you have just 48 hours to sign up for my Comprehensive Database Performance Health Check service at a greatly discounted rate. After January 1st the rates increase by 20%.
IÂ have worked with over 300 different customers on performance tuning issues and I have seen all sorts of things. I have solutions ready and waiting for 99% of the performance problems out in the world.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
3 Comments. Leave new
Another method: zero pad and then sort:
ORDER BY RIGHT(‘00000000000’+NUMBER,12)
The number of zeroes needs to be at least as long as the longest NUMBER. If you need to figure it out at run time:
SELECT @maxlength=MAX(LEN(NUMBER)) FROM #NUMBERS.
Then use RIGHT(REPLICATE(‘0’,@maxlength) +NUMBER, @maxlength)
We often use ORDER BY LEN(Col), Col
This works fine and avoids the overhead of casting to number, however only works if the number is „plain“ (without leading zeros, without decimals)
If the field in the source is a string but you expect a number to be contained in the value then I would use TRY_CAST as in the event of a rogue string being present the CAST in the order by would provoke an error.