SQL Order Alphabetically without preceding zeros for numbers less than 10

Question

Is it possible to order alphabetically in mySQL in the following fashion:

A1
A2
A3
A5
A10
A11
A20
A23
A24

Unfortunately the order that I am getting is ordered as seen below here. Notice how any number before A10 aren't grouped together?

A1
A10
A11
A12
A2 < A2
A20
A23
A24
A3 < A3
A5 < A5

Note: These alphanumeric strings are actually postal codes, so I cannot put A01, because that's not technically a postal code prefix. I would also like to avoid having the user to enter in other data to help the system sort it properly, because my users aren't very web savvy. Also, these alphanumeric strings will not be able to be input into the database in the correct order because they can be deleted and added at anytime.

Answer 1

Create a UDF that converts your varchar into a numeric value. Then use that function in the ORDER BY clause of your query.

Basically your function will assign a numeric value to each row, something like ASCII value of A(or whatever the first letter is) * 100 + the parsed numeric of the rest of the varchar.

Answer 2

One solution is to figure out exactly what the format of a postal code is and then break it up into its parts. Let's say it was state, zip code: so you could have NC27605. Then you can save state, and zip_code separately and have the postal code be a computed value. Then getting them in order would mean sorting on state then zip_code.

This is always considered good style; the point of a database is to have all independent bits of data be independently accessible.

Answer 3

The ordering is alphabetical.

But you want to order them as if they're numbers. Unfortunately ASC and DESC doesn't give much flexibility.

My copy of MySQL, New Riders, 2000, by Paul DuBois (now a little out of date) says

As of MySQL 3.23.2, you may also specify an expression as a sort column. For example, `ORDER BY RAND() returns rows in random order

If this feature is still available, and you can find a built-in function or cast to convert the varchar to an integer, would it be possible to ORDER BY that function? Or, am I reading too much into it?

Hmmm, I think Bill Karwin expresses it best.

Answer 4

You can use an expression to pick out the numeric portion of the string, if it's in a consistent position in the string. Then coerce the numeric string to its integer value by adding zero to it.

...
ORDER BY SUBSTR(postalcode,2)+0