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