Anil Singh | Code-sample.com

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