SQL - Ordering Alphanumeric columns numerically when they contain mostly numeric data

This came up when a customer wanted a House Number address column to be ordered numerically but it contained alphanumeric data (nvarchar, containing values like "1", "2A"). Standard alphanumeric ordering made results look strange, since the values were mostly numeric (e.g. 1, 11, 111, 2, etc).

I thought that correcting this would be very difficult and would slow the query down considerably, but it turned out to be quite simple.

 

 

SELECT * FROM [app_Address]

ORDER BY CASE ISNUMERIC(HouseNumber) WHEN 1 THEN REPLICATE('0', 90 - LEN(HouseNumber)) + HouseNumber ELSE HouseNumber END

 

 

* Where 90 is the size of the alphanumeric column