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:

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'

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

SELECT *
FROM #varchar_field
ORDER BY mixed_field


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.

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

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.

5 comments
sadiq 31 May 2016 at 8:57 am

please tell me how to perform with alphanumeric
2.1lacs
2.0 lacs
36.5 lacs
6.7 lacs
23.5 lacs
6.5 lacs

Thanh Phan 08 Mar 2016 at 9:04 am

this article is useful for me

Jay 29 Aug 2015 at 7:05 am

Google Chrome 20.0.1132.57 Windows XPI blog quite often and I really thank you for your inoaimftron. This great article has really peaked my interest. I will take a note of your website and keep checking for new details about once a week. I subscribed to your Feed too.

Raju 17 Apr 2013 at 7:51 am

Its not working when we specify the DISTINCT keyword in the select list.

SELECT DISTINCT mixed_field
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

Emi 29 Aug 2015 at 6:12 am

Google Chrome 4.0.221.7 Windows 7Hey there! I just wanted to ask if you ever have any tourble with hackers? My last blog (wordpress) was hacked and I ended up losing many months of hard work due to no backup. Do you have any methods to protect against hackers?

Featured Articles

 Site Author

  • Thanks for visiting!