How to Sort a Varchar Column Storing Integers with Order By?

How to Sort a Varchar Column Storing Integers with Order By? SQL Server

Recently I faced a very interesting issue during the column sorting in SQL Server. I have a VARCHAR column in a SQL Server database that can contain either letters or numbers. It depends on how the application is configured on the front-end for the employee codes.

One possible solution is to pad the numeric values with a character in front so that all are of the same 'string' length.

As an Example,

SELECT TOP 15 *
FROM [DBO].[EMP_CODES]
ORDER BY
CASE ISNUMERIC(EMP_CODE)
    WHEN 1 THEN REPLICATE('0', 100 - LEN(LTRIM(RTRIM(EMP_CODE)))) + LTRIM(RTRIM(EMP_CODE))
    ELSE LTRIM(RTRIM(EMP_CODE))
END

Second possible solution

As an Example,

SELECT TOP 15 *
FROM [DBO].[EMP_CODES]
ORDER BY
CASE WHEN ISNUMERIC(EMP_CODE) = 1 THEN RIGHT(REPLICATE('0',21) + LTRIM(RTRIM(EMP_CODE)), 20)
     WHEN ISNUMERIC(EMP_CODE) = 0 THEN LEFT(LTRIM(RTRIM(EMP_CODE)) + REPLICATE('',21), 20)
     ELSE LTRIM(RTRIM(EMP_CODE))
END

Both the solutions are working as per expected result. If you have any other methods, text me I will add the 3rd possible solutions. I hope these examples will helps to another one to resolve the issue.

ANIL SINGH

Anil Singh is an author, tech blogger, and software programmer. Book writing, tech blogging is something do extra and Anil love doing it. For more detail, kindly refer to this link..

My Tech Blog - https://www.code-sample.com/
My Books - Book 1 and Book 2

www.code-sample.com/. Powered by Blogger.
^