How to sort a text box in alphabetical order?

Question

I have a couple of records that need to be sorted, but the field is varchar. They use the English alphabet from AZ. After Z, it comes 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'; 

As a result, I want:

 A B Z AA AB BB 

I know that I MUST use the number field and sort by it, but at the moment I do not have this option. I am using SQL Server and the front end is in an Access 2010 report.

Answer 1

It might work. You can sort by the length of the value that will group single characters, followed by double characters. Within these groups, the values ​​will be sorted alphabetically.

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

Answer 2

Ok, I'm a little confused. Sounds like you need a funky sort order. First you need separate letters, and THEN the normal sort order. (A, AA, AB, AC, then B will be completely normal)

First simple sorting by alphabetical field

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

But that is not what you want. The problem is that you never tell us where you want AAA. Does this happen 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 

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 it should still work.

Results:

 val --- a b c ab az zz abc1 

Answer 4

Here is an example

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

Using ASC column order allows you to sort this column alphabetically