Ordering mixed Alpha and Digit Characters

Suppose you have a VARCHAR column that has a mix of alpha and digit characters. In other words, the values represent different types across rows.

Like this:

SELECT   mixedColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)

Which produces:

So how does this get sorted, by default?

SELECT   mixedAlphaColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedAlphaColumn)
ORDER BY mixedAlphaColumn

Which produces:

This gets ordered based on its ASCII code value. Since all ASCII digit characters come before alpha characters, digits will come before alpha when using ORDER BY.  You can see this with this code:

SELECT   mixedColumn,
         ASCII(LEFT(mixedColumn, 1)) AS [ASCII Value]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY mixedColumn

Which produces:

But what if we want to have the digit characters come after the alpha characters?

We could do this:

SELECT   mixedColumn,
         ASCII(LEFT(mixedColumn, 1)) AS [ASCII Value]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY mixedColumn DESC

Which produces this:

But what if we want the digits ordered ascending, and the alpha ordered ascending, but the alpha before the digits?

Here's the trick - the ISNUMERIC function.

SELECT   mixedColumn,
         ISNUMERIC(mixedColumn) AS [isNumeric]
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY ISNUMERIC(mixedColumn),
         mixedColumn

Which produces:

And of course, we don't need to display the isNumeric column:

SELECT   mixedColumn
FROM     (VALUES ('fred'), 
             (CONVERT (VARCHAR, 1)), 
             (CONVERT (VARCHAR, 3)), 
             ('george'), 
             (CONVERT (VARCHAR, 7)), 
             ('ginger') 
         ) AS myTable(mixedColumn)
ORDER BY ISNUMERIC(mixedColumn),
         mixedColumn

Which produces:

The ISNUMERIC function became available in SQL 2008. However, be careful, since certain characters that you might expect to be alpha are actually interpreted as digit, e.g. periods, commas, plus sign, etc.