Search code examples
sql-serverms-access

How do I sort an alphabetical text field?

I have a couple of records that need to be sorted but the field is varchar. They use the English Alphabet from A-Z. After Z it goes from AA, AB, AC, etc… for example:

CREATE TABLE #foo(x VARCHAR(30));

INSERT #foo(x) SELECT 'A'
UNION SELECT 'AA'
UNION SELECT 'Z'
UNION SELECT 'B'
UNION SELECT 'AB'
UNION SELECT 'BB';

The ordering I want in the result is:

A
B
Z
AA
AB
BB

I know I SHOULD use a number field and sort by that, but at the moment I don’t have that option. I am using SQL Server and the front end is on a report in Access 2010.

Solution

  • This might work. You can sort by length of the value which will group the single characters followed by double characters. Within those groups the values will be sorted alphabetically.

    SELECT      Column1 
FROM        dbo.Table1 
ORDER BY    LEN(Column1)
        ,   Column1