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 momen开发者_开发知识库t I don’t have that option. I am using SQL Server and the front end is on a report in Access 2010.

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

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

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

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