Ordering String As Numbers -- Sometimes

We have a varchar(50) in our database called item number. Most of our customers use this as a number, entering numbers. However, some of them for flexiablity (not all square items go into round holes) want to use it as the abstract number, entering in things like: "SC-1002S". The issue came up the other day, that a majority of our users want the numbers they add to sort like numbers, we were sorting this like strings: (1,11,2,22,23,3 ...). So Steve came up with this great idea of a double sort, first trying to CONVERT the string to a number. Here is my T-SQL for the idea: SELECT * FROM Items ORDER BY (CASE WHEN ((ISNUMERIC(ItemNumber) = 1) AND (NOT ItemNumber LIKE '%E%') AND (NOT ItemNumber LIKE '%.%')) THEN CONVERT(int, ItemNumber) ELSE NULL END), ItemNumber First thing to note is that you can use a CASE expression in an ORDER BY Clause. Second thing is that first we try to convert the string to a number, if we can't it is a NULL, secondarly we sort it as a string. {6230289B-5BEE-409e-932A-2F01FA407A92}