CODEWITHSUNDEEP
sqlsql-serversortingalphanumeric
AlexVPerl
AlexVPerl

Reputation: 7996

SQL Alphanumeric Sort on VARCHAR Column

I'm dealing with alpha numeric column values that need to be sorted by letters and then by numbers.

Example of data:

Needs to be sorted as follows:

Need advice on how to sort this in SQL.

Thanks.

Upvotes: 2

Views: 368

Answers (2)

Thejaka Maldeniya
Thejaka Maldeniya

Reputation: 1076

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

Upvotes: 1

Elton C Ramos
Elton C Ramos

Reputation: 9

You tried?

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

Upvotes: 0

Related Questions