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?

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

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.

Solarwinds
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

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

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

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

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)

Solarwinds
, , , ,
Previous Post
How to Track Autogrowth of Any Database? – Interview Question of the Week #205
Next Post
When to Use sort_in_tempdb for Rebuilding Indexes? – Interview Question of the Week #207

Related Posts

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)

    Reply
  • 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)

    Reply
  • 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.

    Reply

Leave a Reply

Menu