How to Sort a Varchar Column Storing Integers with Order By? – Interview Question of the Week #206

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://darkslategrey-bat-805937.hostingersite.com)