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

Question: How do you sort a varchar column that stores integers in numeric order?

Answer: Convert the values to a suitable numeric type in ORDER BY. A varchar column sorts as text, so it does not necessarily follow the numeric order you intended.

A senior developer asked this during a performance health check. Changing the column’s data type was the obvious answer, but it was not possible for that application at the time. We worked with the column they had.

IF OBJECT_ID('tempdb..#SqlaNumbers184397') IS NOT NULL
    THROW 50001, 'The sample table already exists in this session.', 1;
CREATE TABLE #SqlaNumbers184397 (NUMBER varchar(10));
INSERT #SqlaNumbers184397 (NUMBER)
VALUES ('8792'), ('199876'), ('9012'), ('876100'), ('2098763');

SELECT NUMBER FROM #SqlaNumbers184397 ORDER BY NUMBER;
SELECT NUMBER FROM #SqlaNumbers184397 ORDER BY CAST(NUMBER AS int);
SELECT NUMBER FROM #SqlaNumbers184397 ORDER BY NUMBER * 1;

-- SQL Server2012+: keep unconvertible text last rather than failing.
SELECT NUMBER FROM #SqlaNumbers184397
ORDER BY CASE WHEN TRY_CONVERT(bigint, NUMBER) IS NULL THEN 1 ELSE 0 END,
         TRY_CONVERT(bigint, NUMBER), NUMBER;
DROP TABLE #SqlaNumbers184397;

The first SELECT returns 199876, 2098763, 876100, 8792, 9012 under the usual digit ordering. It compares the character strings. The CAST and multiplication examples both return 8792, 9012, 199876, 876100, 2098763.

Which expression should you use?

I prefer explicit CAST when all values are valid and fit in int. Multiplying by 1 works because int takes precedence over varchar and SQL Server converts the string, but the conversion is easier to miss when reading the query. Both expressions fail on nonnumeric text or a number outside the int range.

The final SELECT uses TRY_CONVERT, available from SQL Server 2012, and bigint for a wider integer range. Unconvertible values become NULL; the CASE puts those values last instead of silently putting them first. Decide how to handle empty strings, NULLs, decimal text and invalid values according to your application’s rules. This is integer ordering, not a validator for every textual representation of a number.

Applying a conversion can require a sort even when the original text column has an index. For frequent large queries, the lasting answer is a suitable numeric column, validated data, or an appropriately designed persisted computed column and index. The original workaround is useful, but it does not remove the schema issue.

Have you handled this constraint in an existing application? Share the approach that worked for you.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.