If you have ever tried to order numerical values in a varchar field you’ll know that the sort doesn’t occur numerically as it would if the datatype were numeric. In other words, the values of 1 and 10 will be sorted together because they both start with a leading 1. To overcome this, we have to cast the values as numeric. But this raises another issue. Since it’s a varchar field we cannot ensure that the values are all numeric which means we additionally have to take into account characters.
Let’s start by creating a sample table and populating some values:
ID INT IDENTITY(1,1),
INSERT INTO #varchar_field (mixed_field)
Now let’s run a simple sort so we can see the default behavior.
ORDER BY mixed_field
Now let’s execute a revised version of this order by.
WHEN ISNUMERIC(mixed_field) = 1 THEN CAST(mixed_field AS FLOAT)
WHEN ISNUMERIC(LEFT(mixed_field,1)) = 0 THEN ASCII(LEFT(LOWER(mixed_field),1))
Here we check to see if the field is numeric first. If it is, we cast it to float to deal with potential decimals. If it’s not numeric we simply take the ASCII value of the leading character and sort by that. This allows us to also sort the alpha characters within the field.
If the value turns out to be not caught we use the maximum value for an integer.