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)