How do I sort an alphabetical text field?

Question

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.

Neeley

I added table structure and the desired results for @Rick. – Horst

Answer 1

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

Starchy

Answer 2

OK, I a bit confused. It sounds like you want a funky sort order. You want single letters first, and THEN normal sort order. (A fully normal sort would have A, AA, AB, AC, then B)

First, simple sorting on a alphabetic field

 select SORTFIELD, OTHER, COLUMNS, IN-TABLE
 from  MYTABLE
 order by SORTFIELD

But that's not what you want. The problem is you never tell us where you want AAA. Does it go after AA or after ZZ ?

AAA After ZZ:

 select SORTFIELD, OTHER, COLUMNS, IN-TABLE
 from  MYTABLE
 order by LEN(SORTFIELD), SORTFIELD

AAA After AA (before AB)

 select SORTFIELD, OTHER, COLUMNS, IN-TABLE
 from  MYTABLE
 order by case LEN(SORTFIELD) when 1 then 0 else 1 end , SORTFIELD

Skeptic

Answer 3

Declare @SomeStuff table (val varchar(10));

Insert @SomeStuff (val) Values ('a');
Insert @SomeStuff (val) Values ('b');
Insert @SomeStuff (val) Values ('c');
Insert @SomeStuff (val) Values ('az');
Insert @SomeStuff (val) Values ('ab');
Insert @SomeStuff (val) Values ('zz');
Insert @SomeStuff (val) Values ('abc1');


Select * From @SomeStuff order by LEN(val), val

Uses SQL Server but should still work.

Results:

val
---
a
b
c
ab
az
zz
abc1

Glendaglenden

Answer 4

Here is an example for you

Select *
from MyTable
Order By foo ASC, foo2 ASC, foo3 ASC

Using the Order by column ASC will allow you to order that column alphabetically

Butte

Answer 5

I use a function len to count all characters in the field and order alphabetically with this function

(
     ORDER BY  
     case when @orderArmario = 0 AND @orderLocal = 0 then '' end ,
     case when @orderArmario = 1 then LEN(e.Armario) end asc,
     case when @orderArmario = 1 then e.Armario end asc,
     case when @orderArmario = 2 then LEN(e.Armario) end desc,
     case when @orderArmario = 2 then e.Armario end desc,
     case when @orderLocal = 1 then LEN(e.local) end asc,
     case when @orderLocal = 1 then e.local end asc,
     case when @orderLocal = 2 then LEN(e.local) end desc,
     case when @orderLocal = 2 then e.local end desc
)

Septuagint