SQL Alphanumeric Sort on VARCHAR Column

The below may work for alphanumerics. However be warned that the behaviour is undefined for non alphanumeric values.

WITH A (A, N) AS (
    SELECT A, 3 + LEN(A) FROM (
        SELECT 'a1'
        UNION ALL
        SELECT 'a10'
        UNION ALL
        SELECT 'a11'
        UNION ALL
        SELECT 'ab2'
        UNION ALL
        SELECT 'ab21'
        UNION ALL
        SELECT 'ab22'
        UNION ALL
        SELECT 'a1a'
        UNION ALL
        SELECT 'a1b'
        UNION ALL
        SELECT 'a1'
        UNION ALL
        SELECT 'a1a'
        UNION ALL
        SELECT 'a1b'
        UNION ALL
        SELECT 'a2'
        UNION ALL
        SELECT 'a3'
        UNION ALL
        SELECT 'a9'
        UNION ALL
        SELECT 'ab1'
        UNION ALL
        SELECT 'ab2'
        UNION ALL
        SELECT 'ab3'
    ) T (A)
), B (A, N, I, C, D, X) AS (
    SELECT A, N, 3, CAST(SUBSTRING(A, 1, 1) AS VARCHAR(255)), CAST(SUBSTRING(A, 2, 1) AS VARCHAR(255)), CAST('' AS VARCHAR(255)) FROM A
    UNION ALL
    SELECT A, N, I + 1, D, CAST(SUBSTRING(A, I, 1) AS VARCHAR(255)), CASE WHEN ASCII(C) BETWEEN 48 AND 57 AND ASCII(D) BETWEEN 48 AND 57 THEN CAST(X + CHAR(10 + ASCII(D)) AS VARCHAR(255)) WHEN 58 > ASCII(C) THEN CAST(X + C AS VARCHAR(255)) ELSE CAST(X + CHAR(3 + ASCII(C)) AS VARCHAR(255)) END FROM B WHERE I <= N
)
SELECT A FROM B WHERE I = N
ORDER BY X COLLATE Latin1_General_BIN

The code currently sorts in a Case Sensitive manner, but may be tweaked to do a case insensitive sort as well, for example by using

ORDER BY UPPER(X) COLLATE Latin1_General_BIN

or

ORDER BY LOWER(X) COLLATE Latin1_General_BIN

You tried?

SELECT column_x
FROM table_name x
ORDER BY x.column_name ASC|DESC, x.column_name ASC|DESC;