How to Order Numeric Values in a Varchar Field

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:
[cc lang=”sql”]
CREATE TABLE #varchar_field
(
ID INT IDENTITY(1,1),
mixed_field varchar(100),
)

INSERT INTO #varchar_field (mixed_field)
SELECT ‘1’
union all
SELECT ‘4.9’
union all
SELECT ’10’
union all
SELECT ’50’
union all
SELECT ‘6’
union all
SELECT ‘a’
union all
SELECT ‘z’
union all
SELECT ‘A’
[/cc]

Now let’s run a simple sort so we can see the default behavior.

[cc lang=”sql”]
SELECT *
FROM #varchar_field
ORDER BY mixed_field
[/cc]

Here we see everything is only sorted by it’s leading character, not taking into consideration the value of the numbers.

Now let’s execute a revised version of this order by.

[cc lang=”sql”]
SELECT *
FROM #varchar_field
ORDER BY
CASE
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))
ELSE 2147483647
END
[/cc]

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.

Here is the new result:

If the value turns out to be not caught we use the maximum value for an integer.