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.
